Re: jsonb_array_elements issue - Mailing list pgsql-bugs

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,"ski=
llName":"[{\"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,"effect=
Data":{"effectFormula":"1*$$X$$","effectId":73,"effectTarget":1,"timeSchedu=
le":""},"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,"skil=
lName":"[{\"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,"effect=
Data":{"effectFormula":"1*$$X$$","effectId":73,"effectTarget":1,"timeSchedu=
le":""},"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.paquier@gmail.com>
wrote:

> On Fri, Aug 5, 2016 at 11:42 PM, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > Nabble generates emails that these lists do not properly accept - all o=
f
> > 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
>