Re: jsonb_array_elements issue

Hi newly Michael,
I repeat my test for confirm your results and basically there are not
errors.

In my previous email I forgot to say that I create a cast in my database as
I made with JSON. The cast is this:

CREATE CAST (text AS JSONB) WITHOUT FUNCTION AS IMPLICIT;



If you create this cast and you launch my select without the explicit cast,
you should get the error. With this cast created I reported some other
issues, so at this point my question is: can I use this implicit cast or is
better to explicit it anytime is needed?

Is it possible that there are issues in implicit cast?

Very kind regards.

Meph

On 16 August 2016 at 09:14, Michael Paquier <michael.paquier@gmail.com>
wrote:

> On Tue, Aug 16, 2016 at 3:17 PM, Mephysto <mephystoonhell@gmail.com>
> wrote:
> > With this JSON:
>
> Thanks.
>
> > {
> > "skillId": 58,
> > "applicationConditionId": 1,
> > "skillName":
> > "[{\"id\":1,\"text\":\"Armatura\"},{\"id\":2,\"text\":\"Armor\"}]",
> > "skillDescription": "[{\"id\":1,\"text\":\"Riduce ATK DMG ricevuto dalla
> > Carta Personaggio di #[$$X$$]#\"},{\"id\":2,\"text\":\"Reduce ATK DMG
> dealt
> > to Character Card by #[$$X$$]#\"}]",
> > "affectsData": [{
> > "activationTime": 1000,
> > "affectId": 0,
> > "affectTarget": 1,
> > "affectTrigger": 2,
> > "afterOrBeforeTriggeringAction": 1,
> > "effectData": {
> > "effectFormula": "1*$$X$$",
> > "effectId": 73,
> > "effectTarget": 1,
> > "timeSchedule": ""
> > },
> > "timesToTrigger": -1
> > }],
> > "affectsData": []
> > }
>
> This is referring twice to affectsData as a key, so with jsonb only
> the empty array is used, which is the second value of affectsData.
> Hence I think that  you mean *effectsData* and not *affectsData* in
> this sample. This matches as well what's written below.
>
> > If I try to execute
> >
> > select
> > jsonb_array_elements(('{"skillId":58,"applicationConditionId":1,"
> skillName":"[{\"id\":1,\"text\":\"Armatura\"},{\"id\":2,\"
> text\":\"Armor\"}]","skillDescription":"[{\"id\":1,\"text\":\"Riduce
> > ATK DMG ricevuto dalla Carta Personaggio di
> > #[$$X$$]#\"},{\"id\":2,\"text\":\"Reduce ATK DMG dealt to Character
> Card by
> > #[$$X$$]#\"}]","affectsData":[{"activationTime":1000,"
> affectId":0,"affectTarget":1,"affectTrigger":2,"
> afterOrBeforeTriggeringAction":1,"effectData":{"effectFormula":"1*$$X$$","
> effectId":73,"effectTarget":1,"timeSchedule":""},"timesToTrigger":-1}],"
> effectsData":[]}'::JSONB)->>
> > 'affectsData')
> > I get this error: ERROR:  unknown type of jsonb container
>
> jsonb_array_elements uses jsonb as input, but ->> returns text, so
> this does not map correctly and would complain about an unmatched
> input type. Note that I still see no problems if I cast the output of
> (jsonb_blob->>'affectsData'::jsonb) or use directly ->.  Well if I
> just use this example I just get back an unmatched input complain.
>
> Still, I have been manipulating your example, but I am afraid that I
> could not reproduce this problem... Attached is a script summarizing
> what I have used. Are you sure that you run 9.5.3? I have played with
> 9.5.0, 9.5.3 and master still things are looking fine from here.
> --
> Michael
>