Обсуждение: jsonb_array_elements issue
Hi there, I found an issue in jsonb_array_elements that can be a bug. I am using PostgreSQL 9.5.3. With this JSON: If I try to execute 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: return this result: Moreover it is strange that jsob function run without errors if I execute it with internal JSON as parameter: returns correct JSON. Is it an error genrate by myself or is it a bug indeed? Thanks in advance. Meph -- View this message in context: http://postgresql.nabble.com/jsonb-array-elements-issue-tp5915026.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Fri, Aug 5, 2016 at 9:39 AM, mephysto <mephystoonhell@gmail.com> wrote:
> Hi there,
> I found an issue in jsonb_array_elements that can be a bug. I am using
> PostgreSQL 9.5.3.
=E2=80=8BNabble generates emails that these lists do not properly accept - =
all of
your json and queries got stripped out.
Anyway, the queries you provided gave me "function not found errors".
I did get the following to work just fine though...
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,"affectTa=
rget":1,"affectTrigger":2,"afterOrBeforeTriggeringAction":1,"effectData":{"=
effectFormula":"1*$$X$$","effectId":73,"effectTarget":1,"timeSchedule":""},=
"timesToTrigger":-1}],"effectsData":[]}'::JSONB
)->>'affectsData'
)::jsonb)
David J.
=E2=80=8B
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 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
Hi Michael, I'm sorry but I'm out for a vacation. I will post my case when I will come back to home. Within a week, maybe. Many thanks. Bye. Meph On 6 Aug 2016 2:17 p.m., "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 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 >
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
>
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
Вложения
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
>
Mephysto <mephystoonhell@gmail.com> writes:
> 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;
Well, that's rather a critical bit of information :-(
> 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?
This cast definition is completely broken; it's astonishing that you
haven't had outright crashes, because text and jsonb do NOT have the
same underlying representation, which is what would be required to
make a cast WITHOUT FUNCTION work correctly. But this certainly
explains 'unknown type of jsonb container' errors.
You could fix that by providing a suitable casting function. I'm still
dubious that making it an implicit cast is a good idea though. The
trouble with implicit casts is that they tend to kick in when you were
not expecting them to. Past experience has suggested a rule of thumb
that implicit cross-type-category casts are best avoided, and I'd
certainly call this a type category crossing.
regards, tom lane
Thank you very much for your explanation Tom. Very kind regards. Meph On 16 Aug 2016 5:56 p.m., "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Mephysto <mephystoonhell@gmail.com> writes: > > 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; > > Well, that's rather a critical bit of information :-( > > > 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? > > This cast definition is completely broken; it's astonishing that you > haven't had outright crashes, because text and jsonb do NOT have the > same underlying representation, which is what would be required to > make a cast WITHOUT FUNCTION work correctly. But this certainly > explains 'unknown type of jsonb container' errors. > > You could fix that by providing a suitable casting function. I'm still > dubious that making it an implicit cast is a good idea though. The > trouble with implicit casts is that they tend to kick in when you were > not expecting them to. Past experience has suggested a rule of thumb > that implicit cross-type-category casts are best avoided, and I'd > certainly call this a type category crossing. > > regards, tom lane >
On Wed, Aug 17, 2016 at 12:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Mephysto <mephystoonhell@gmail.com> writes: >> 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; > > Well, that's rather a critical bit of information :-( > >> 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? > > This cast definition is completely broken; it's astonishing that you > haven't had outright crashes, because text and jsonb do NOT have the > same underlying representation, which is what would be required to > make a cast WITHOUT FUNCTION work correctly. But this certainly > explains 'unknown type of jsonb container' errors. > > You could fix that by providing a suitable casting function. I'm still > dubious that making it an implicit cast is a good idea though. The > trouble with implicit casts is that they tend to kick in when you were > not expecting them to. Past experience has suggested a rule of thumb > that implicit cross-type-category casts are best avoided, and I'd > certainly call this a type category crossing. Just to put an extra stone on that. json is represented on-disk as a text blob, which is why you are not seeing problems with it. Still, using directly casting with :: in your SQL queries would prove to be more robust in the long term. -- Michael