Re: BUG #15242: JSON functions not recognizing JSON

On Thursday, June 14, 2018, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15242
> Logged by: David
> Email address: david(at)daily-harvest(dot)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.