Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #15242: JSON functions not recognizing JSON

BUG #15242: JSON functions not recognizing JSON

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15242
Logged by:          David
Email address:      david@daily-harvest.com
PostgreSQL version: 9.6.6
Operating system:   Mac / DBeaver
Description:

I get this error:   SQL Error [22023]: ERROR: cannot deconstruct an array as
an object

when running the statement below because the JSON_EACH function doesn't
recognize the JSON. If I remove the json_each function the query runs fine
and executes JSON_ARRAY_LENGTH on the same variable, even though both
functions take json.

WITH sample_text AS 
(
SELECT TEXT '[{"id":11,"name":"Chocolate +
Blueberry","sku":"11-CHOBLU"},{"id":11,"name":"Chocolate +
Blueberry","sku":"11-CHOBLU"}]' AS txt
)
SELECT json_array_length(txt::json), json_each(txt::json) FROM sample_text

Re: BUG #15242: JSON functions not recognizing JSON

From
"David G. Johnston"
Date:
On Thursday, June 14, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15242
Logged by:          David
Email address:      david@daily-harvest.com
PostgreSQL version: 9.6.6
Operating system:   Mac / DBeaver
Description:       

I get this error:   SQL Error [22023]: ERROR: cannot deconstruct an array as
an object

when running the statement below because the JSON_EACH function doesn't
recognize the JSON. If I remove the json_each function the query runs fine
and executes JSON_ARRAY_LENGTH on the same variable, even though both
functions take json.

WITH sample_text AS
(
SELECT TEXT '[{"id":11,"name":"Chocolate +
Blueberry","sku":"11-CHOBLU"},{"id":11,"name":"Chocolate +
Blueberry","sku":"11-CHOBLU"}]' AS txt
)
SELECT json_array_length(txt::json), json_each(txt::json) FROM sample_text

This is not a bug, nor is the behavior wrong.

json_each requires that the json being passed to it is a json object, not a json array.  The docs and the error message say as much.

As json_each outputs two columns, one with the key and the other with the value, and since arrays don't have keys, it follows that json_each would be unable to operate on them.

David J.

Re: BUG #15242: JSON functions not recognizing JSON

From
Andrew Gierth
Date:
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> I get this error: SQL Error [22023]: ERROR: cannot deconstruct an
 PG> array as an object

 PG> when running the statement below because the JSON_EACH function
 PG> doesn't recognize the JSON.

It recognizes the json just fine, and so it can tell that it's an array
rather than the expected object. You were looking for
json_array_elements rather than (or prior to) json_each if you want to
enumerate the objects:

-- to get one row per object:

select json_array_length(txt::json), a.value
  from sample_text, json_array_elements(txt::json) as a;

-- to get one row per object key:

select json_array_length(txt::json), a.value, a.ord, e.key, e.value
  from sample_text,
       json_array_elements(txt::json) with ordinality as a(value,ord),
       json_each(a.value) as e;

-- 
Andrew (irc:RhodiumToad)