Обсуждение: [GENERAL] Querying JSON Lists
Hello everyone,
playing around with jsonb and coming from this SO question http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string I wonder why PostgreSQL behaves differently for text and integers on the ? and @> operators.
Let's have a look at 4 different but similar queries:
-- A) ? + text
select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12';
?column?
----------
t
-- B) ? + integer
select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
ERROR: operator does not exist: jsonb ? integer
LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
-- C) @> + text
select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]', '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food": ["12","34","45"]}'::jsonb->'food' @> '12';
?column? | ?column? | ?column?
----------+----------+----------
t | t | f
-- D) @> + integer
select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food": [12,34,45]}'::jsonb->'food' @> '12';--, '{"food": [12,34,45]}'::jsonb->'food' @> 12;
?column? | ?column?
----------+----------
t | t
Now my questions:
1) Why does A) work? Docs tells us that ? works for keys, not values.
2) Why does B) not work although A) works?
3) Why do the variants without the brackets on the right side of @> work in C) and D)? Is there json data where their results differ from the ones with the brackets?
4) What is the recommended way of testing inclusion in json lists?
Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html
Regards,
Sven
playing around with jsonb and coming from this SO question http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string I wonder why PostgreSQL behaves differently for text and integers on the ? and @> operators.
Let's have a look at 4 different but similar queries:
-- A) ? + text
select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12';
?column?
----------
t
-- B) ? + integer
select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
ERROR: operator does not exist: jsonb ? integer
LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
-- C) @> + text
select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]', '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food": ["12","34","45"]}'::jsonb->'food' @> '12';
?column? | ?column? | ?column?
----------+----------+----------
t | t | f
-- D) @> + integer
select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food": [12,34,45]}'::jsonb->'food' @> '12';--, '{"food": [12,34,45]}'::jsonb->'food' @> 12;
?column? | ?column?
----------+----------
t | t
Now my questions:
1) Why does A) work? Docs tells us that ? works for keys, not values.
2) Why does B) not work although A) works?
3) Why do the variants without the brackets on the right side of @> work in C) and D)? Is there json data where their results differ from the ones with the brackets?
4) What is the recommended way of testing inclusion in json lists?
Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html
Regards,
Sven
On 02/26/2017 03:26 AM, Sven R. Kunze wrote: > Hello everyone, > > playing around with jsonb and coming from this SO question > http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string > I wonder why PostgreSQL behaves differently for text and integers on the > ? and @> operators. > > > Let's have a look at 4 different but similar queries: > > -- A) ? + text > select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12'; > ?column? > ---------- > t > > -- B) ? + integer > select '{"food": [12,34,45]}'::jsonb->'food' ? 12; > ERROR: operator does not exist: jsonb ? integer > LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12; > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT "jsonb also has an existence operator, which is a variation on the theme of containment: it tests whether a string (given as a text value) appears as an object key or array element at the top level of the jsonb value. These examples return true except as noted -- String exists as array element: SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; " > > -- C) @> + text > select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]', > '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food": > ["12","34","45"]}'::jsonb->'food' @> '12'; > ?column? | ?column? | ?column? > ----------+----------+---------- > t | t | f > > -- D) @> + integer > select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food": > [12,34,45]}'::jsonb->'food' @> '12';--, '{"food": > [12,34,45]}'::jsonb->'food' @> 12; > ?column? | ?column? > ----------+---------- > t | t > > > Now my questions: > > 1) Why does A) work? Docs tells us that ? works for keys, not values. > 2) Why does B) not work although A) works? > 3) Why do the variants without the brackets on the right side of @> work > in C) and D)? Is there json data where their results differ from the > ones with the brackets? > 4) What is the recommended way of testing inclusion in json lists? I have not worked through your examples, but I suspect the answer's lie here: https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT 8.14.3. jsonb Containment and Existence > > Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html > > Regards, > Sven -- Adrian Klaver adrian.klaver@aklaver.com
On 28.02.2017 17:33, Adrian Klaver wrote: > On 02/26/2017 03:26 AM, Sven R. Kunze wrote: >> Hello everyone, >> >> playing around with jsonb and coming from this SO question >> http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string >> >> I wonder why PostgreSQL behaves differently for text and integers on the >> ? and @> operators. >> >> >> Let's have a look at 4 different but similar queries: >> >> -- A) ? + text >> select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12'; >> ?column? >> ---------- >> t >> >> -- B) ? + integer >> select '{"food": [12,34,45]}'::jsonb->'food' ? 12; >> ERROR: operator does not exist: jsonb ? integer >> LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12; >> ^ >> HINT: No operator matches the given name and argument type(s). You >> might need to add explicit type casts. > > https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT > > > "jsonb also has an existence operator, which is a variation on the > theme of containment: it tests whether a string (given as a text > value) appears as an object key or array element at the top level of > the jsonb value. These examples return true except as noted > > -- String exists as array element: > SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; > " > >> >> -- C) @> + text >> select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]', >> '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food": >> ["12","34","45"]}'::jsonb->'food' @> '12'; >> ?column? | ?column? | ?column? >> ----------+----------+---------- >> t | t | f >> >> -- D) @> + integer >> select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food": >> [12,34,45]}'::jsonb->'food' @> '12';--, '{"food": >> [12,34,45]}'::jsonb->'food' @> 12; >> ?column? | ?column? >> ----------+---------- >> t | t >> >> >> Now my questions: >> >> 1) Why does A) work? Docs tells us that ? works for keys, not values. >> 2) Why does B) not work although A) works? >> 3) Why do the variants without the brackets on the right side of @> work >> in C) and D)? Is there json data where their results differ from the >> ones with the brackets? >> 4) What is the recommended way of testing inclusion in json lists? > > I have not worked through your examples, but I suspect the answer's > lie here: > > https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT > > > 8.14.3. jsonb Containment and Existence More details yes, but not really an explanation on the 'why'. Especially not on 2) and 3). These feel like holes in the implementation. Sven
On 03/02/2017 01:09 PM, Sven R. Kunze wrote: > > > On 28.02.2017 17:33, Adrian Klaver wrote: >> On 02/26/2017 03:26 AM, Sven R. Kunze wrote: >>> Hello everyone, >>> >>> playing around with jsonb and coming from this SO question >>> http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string >>> >>> I wonder why PostgreSQL behaves differently for text and integers on the >>> ? and @> operators. >>> >>> >>> Let's have a look at 4 different but similar queries: >>> >>> -- A) ? + text >>> select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12'; >>> ?column? >>> ---------- >>> t >>> >>> -- B) ? + integer >>> select '{"food": [12,34,45]}'::jsonb->'food' ? 12; >>> ERROR: operator does not exist: jsonb ? integer >>> LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12; >>> ^ >>> HINT: No operator matches the given name and argument type(s). You >>> might need to add explicit type casts. >> >> https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT >> >> >> "jsonb also has an existence operator, which is a variation on the >> theme of containment: it tests whether a string (given as a text >> value) appears as an object key or array element at the top level of >> the jsonb value. These examples return true except as noted >> >> -- String exists as array element: >> SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; >> " >> >>> >>> -- C) @> + text >>> select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]', >>> '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food": >>> ["12","34","45"]}'::jsonb->'food' @> '12'; >>> ?column? | ?column? | ?column? >>> ----------+----------+---------- >>> t | t | f >>> >>> -- D) @> + integer >>> select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food": >>> [12,34,45]}'::jsonb->'food' @> '12';--, '{"food": >>> [12,34,45]}'::jsonb->'food' @> 12; >>> ?column? | ?column? >>> ----------+---------- >>> t | t >>> >>> >>> Now my questions: >>> >>> 1) Why does A) work? Docs tells us that ? works for keys, not values. >>> 2) Why does B) not work although A) works? >>> 3) Why do the variants without the brackets on the right side of @> work >>> in C) and D)? Is there json data where their results differ from the >>> ones with the brackets? >>> 4) What is the recommended way of testing inclusion in json lists? >> >> I have not worked through your examples, but I suspect the answer's >> lie here: >> >> https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT >> >> >> 8.14.3. jsonb Containment and Existence > > More details yes, but not really an explanation on the 'why'. Especially > not on 2) https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT "-- Object containing pairs of keys and values -- Note that object keys must always be quoted strings SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;" Which I believe comes from: https://tools.ietf.org/html/rfc7159 "4. Objects An object structure is represented as a pair of curly brackets surrounding zero or more name/value pairs (or members). A name is a string. A single colon comes after each name, separating the name from the value. A single comma separates a value from a following name. The names within an object SHOULD be unique. .... " 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. and 3). These feel like holes in the implementation. As to this: test=> select '["12","34","45"]'::jsonb @> '"12"'; ?column? ---------- t " As a special exception to the general principle that the structures must match, an array may contain a primitive value: -- This array contains the primitive string value: SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb; -- This exception is not reciprocal -- non-containment is reported here: SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false " 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. > > Sven > -- Adrian Klaver adrian.klaver@aklaver.com
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
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.klaver@aklaver.com