Re: BUG #15242: JSON functions not recognizing JSON - Mailing list pgsql-bugs

>>>>> "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)