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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
