Re: jsonb_array_elements issue

Hi Michael,
as I promise this is my test case:

With this JSON:

{
"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": []
}

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

Instead, If I use json_array_elements with the same argument, I get no
errors:

select json_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')

return this result:

{"affectId": 0, "effectData": {"effectId": 73, "effectTarget": 1,
"timeSchedule": "", "effectFormula": "1*$$X$$"}, "affectTarget": 1,
"affectTrigger": 2, "activationTime": 1000, "timesToTrigger": -1,
"afterOrBeforeTriggeringAction": 1}

Moreover it is strange that jsob function run without errors if I execute
it with internal JSON as parameter:

select jsonb_array_elements('[{"affectId": 0, "effectData":
{"effectId": 73, "effectTarget": 1, "timeSchedule": "",
"effectFormula": "1*$$X$$"}, "affectTarget": 1, "affectTrigger": 2,
"activationTime": 1000, "timesToTrigger": -1,
"afterOrBeforeTriggeringAction": 1}]')

returns correct JSON.

Thanks in advance.

Meph

On 6 August 2016 at 14:17, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Fri, Aug 5, 2016 at 11:42 PM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > Nabble generates emails that these lists do not properly accept - all of
> > your json and queries got stripped out.
>
> If you are able to hit this error, it would be good to have a
> reproducible test case. I have just scanned the code of
> jsonb_array_elements/elements_worker_jsonb without seeing anything
> weird.
> --
> Michael
>