Re: Querying JSON Lists

On 03/03/2017 11:50 AM, Sven R. Kunze wrote:
> 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

With the caveat:

https://docs.python.org/3/library/json.html#py-to-json-table
"
Note

Keys in key/value pairs of JSON are always of the type str. When a
dictionary is converted into JSON, all the keys of the dictionary are
coerced to strings. As a result of this, if a dictionary is converted
into JSON and then back into a dictionary, the dictionary may not equal
the original one. That is, loads(dumps(x)) != x if x has non-string keys.
"

I know because it's bit me.

I use Python and I get a lot done with it, but it has its
inconsistencies also:

In [11]: d = {1: 'one', 2: 'two'}

In [12]: dict(**d)
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-12-71e1112ea7b0> in <module>()
----> 1 dict(**d)

TypeError: keyword arguments must be strings

In [13]: d = {'1': 'one', '2': 'two'}

In [14]: dict(**d)
Out[14]: {'1': 'one', '2': 'two'}

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

It can be there if you want to maintain readability in your code:

test=> select '["12","34","45"]'::jsonb @> '["12"]';
?column?
----------
t

or you can use an explicit cast:

test=> select '["12","34","45"]'::jsonb @> '"12"'::jsonb;
?column?
----------
t

>
>
> Sven

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com