From: | Mephysto <mephystoonhell(at)gmail(dot)com> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: jsonb_array_elements issue |
Date: | 2016-08-16 06:17:30 |
Message-ID: | CAG0sfBXgzw6bHwEBur=ivnO+nz+jE-7pZ6soHN31OOFm_CS_LQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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,"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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-08-16 07:14:21 | Re: jsonb_array_elements issue |
Previous Message | Tom Lane | 2016-08-15 13:41:46 | Re: 9.5.3: substring: regex greedy operator not picking up chars as expected |