Re: Querying JSON Lists

On 03.03.2017 16:05, Adrian Klaver wrote:
> https://www.postgresql.org/docs/9.6/static/functions-json.html
>
> As to why it works on JSON arrays:
>
> Table 9-43. Additional jsonb Operators
> "
> ? text Does the string exist as a top-level key within the
> JSON value?
> "
>
> So to be picky it not does call out JSON object it says JSON value.
> And right above the table:
>
> " For a full description of jsonb containment and existence semantics,
> see Section 8.14.3. Section 8.14.4 describes how these operators can
> be used to effectively index jsonb data."
>
> As to how that behavior was decided on I have no idea, it just is.

I think it would even be possible to add the integer-variant of the ?
operator.

Something I learned right now: integers cannot be object keys in json.
On the flip side, they can be array elements. So, I can see a certain
logic because of a uncertainty of integers.

Python differs here from PostgreSQL:

>>> json.dumps({4: '34'})
'{"4": "34"}'

>>>># select '{4:4}'::jsonb;
ERROR: invalid input syntax for type json
LINE 1: select '{4:4}'::jsonb;
^
DETAIL: Expected string or "}", but found "4".
CONTEXT: JSON data, line 1: {4...

Python wraps it up, PostgreSQL fails loudly. Not that PostgreSQL is
confused by Python, but it's weird when ? operator works on both keys
and arrays with text but works only on arrays with integers. So, I guess
no support for integers for now might have seen like a good idea.

> Though there looks to be some implicit casting going on:
>
> test=> select '["12","34","45"]'::jsonb @> '"12"'::text;
>
> ERROR: operator does not exist: jsonb @> text
>
> LINE 1: select '["12","34","45"]'::jsonb @> '"12"'::text;
>
> to get '"12"' to be '"12"'::jsonb.
>
> As to why, I don't know.

This makes sense to me at least, as we test structural json containment.
So, testing json to be contained by other json requires it to be json. :)

The confusing fact is that one can omit the array brackets in case of a
single primitive value. Don't get me wrong. I don't complain as it's a
usability feature. However I didn't expect it to be there in the first
place and adding some brackets wouldn't hurt IMO. I'd rather consider
brackets a readability support such as "this is json".

Sven