Re: [GENERAL] Querying JSON Lists
От | Adrian Klaver |
---|---|
Тема | Re: [GENERAL] Querying JSON Lists |
Дата | |
Msg-id | 856b4689-9656-de9e-f8ad-0cc6eb55745f@aklaver.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] Querying JSON Lists ("Sven R. Kunze" <srkunze@mail.de>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Следующее
От: George NeunerДата:
Сообщение: Re: [GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from