Re: jsonb_array_elements issue

On Tue, Aug 16, 2016 at 3:17 PM, Mephysto <mephystoonhell(at)gmail(dot)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