1

Topic: Condition WHERE inside JSON fields postgresql?

In basis there are records
id: 3024
data: ["Egor", "[{\" text \": \" Central African republic CAF \", \" value \": \" 2696 \"}]"]
id: 3025
data: ["Oleg", "[{\" text \": \" Egypt \", \" value \": \" 3341 \"}]"]
How to make request, to select only records with value value
2696 (Central African republic)
Which are inside JSON field records data?

2

Re: Condition WHERE inside JSON fields postgresql?

manking;

SELECT
id;
json_data.*
FROM
table, jsonb_each (table.data:: jsonb) AS json_data
WHERE value = ' value'
AND key = 2696

3

Re: Condition WHERE inside JSON fields postgresql?

Alex URS wrote:

manking;

SELECT
id;
json_data.*
FROM
table, jsonb_each (table.data:: jsonb) AS json_data
WHERE value = ' value'
AND key = 2696

Writes
Error SQL:
ERROR: incorrect syntax for type json
LINE 6: WHERE value = ' value'
^
DETAIL: the Erratic element of the text "value".
CONTEXT: data JSON, a line 1: value
In the operator:
SELECT
id;
json_data.*
FROM
directories_data, jsonb_each (directories_data.data:: jsonb) AS json_data
WHERE value = ' value'
AND key = 2696

4

Re: Condition WHERE inside JSON fields postgresql?

manking;
Thought you will guess...
At you json: [array json]

WITH tbl AS (
select 3024:: integer as id, json_build_object (' data ', json_build_array (' Egor ', json_build_object (' text ', ' Central African republic CAF ', ' value ', 2696))) as dt
union all
select 3025:: integer as id, json_build_object (' data ', json_build_array (' Oleg ', json_build_object (' text ', ' Egiped ', ' value ', 3341))) as dt
) SELECT id, dt
FROM tbl, json_each (tbl.dt:: json) AS json_data;
LATERAL json_array_elements (
json_data.value
) segment
WHERE segment->> ' value ' = ' 2696'

5

Re: Condition WHERE inside JSON fields postgresql?

manking wrote:

In basis there are records
id: 3024
data: ["Egor", "[{\" text \": \" Central African republic CAF \", \" value \": \" 2696 \"}]"]
id: 3025
data: ["Oleg", "[{\" text \": \" Egypt \", \" value \": \" 3341 \"}]"]
How to make request, to select only records with value value
2696 (Central African republic)
Which are inside JSON field records data?

https://github.com/postgrespro/jsquery/- the unit allows SQL to search INSIDE JSONB
Without it will scribble requests about all screen
And with it it is simple:

SELECT * FROM js_test WHERE value @@ '*.color = "red" '; - to find something 

Normal jsonb is not able to search effectively further a key

6

Re: Condition WHERE inside JSON fields postgresql?

But generally  advise separate columns to do, if search in them is necessary

7

Re: Condition WHERE inside JSON fields postgresql?

manking wrote:

normal jsonb is not able to search effectively further a key

It is possible for me on more in detail, what you mean?
PS for jsquery thanks

8

Re: Condition WHERE inside JSON fields postgresql?

GIN
Is able:>?, ?& and? |
The request should be on the upper keys of a tree
Can query nested objects, but only in paths rooted at the top level .

In your case it: id, data

wrote:

The non-default GIN operator class jsonb_path_ops supports indexing the> operator only. An example of creating an index with this operator class is:
# !!! Such index is able only>, but it faster and is less, than standard jsonb_ops

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

Let's consider a table example in which documents JSON received from an indirect web service, with documentary determination of the circuit are stored. The typical document:

{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a";
"name": "Angela Barton";
"is_active": true;
"company": "Magnafone";
"address": "178 Howard Place, Gulf, Washington, 702";
"registered": "2009-11-07T08:53:22 +08:00";
"latitude": 19.793713;
"longitude": 86.513373;
"tags": [
"enim";
"aliquip";
"qui"
]
}

We save these documents in the table api, in a column jdoc type jsonb. If on this column there is a GIN-index, it can be applied in similar requests:
-- To find documents in which the key "company" matters "Magnafone"

SELECT jdoc->'guid ', jdoc->'name ' FROM api WHERE jdoc> ' {"company": "Magnafone"} ';

However, in following requests it will not be used, because, in spite of the fact that the operator? - Indexed, it is applied not to an indexed column jdoc immediately:
-- To find documents in which the key "tags" contains a key or an array cell "qui"

SELECT jdoc->'guid ', jdoc->'name ' FROM api WHERE jdoc-> ' tags '? ' qui ';

And still, correctly applying indexes of expressions, it is possible to involve an index in this request. If the request of certain elements in a key "tags" is fulfilled often, possibly it is necessary to define.
Such index:

CREATE INDEX idxgintags ON api USING GIN ((jdoc-> ' tags'));

-- Now sentence WHERE jdoc-> ' tags '? ' qui ' it will be fulfilled as application of the indexable operator? To indexable expression jdoc-> ' tags'.
One more approach to usage of checks on existence:
-- To find documents in which the key "tags" contains an array cell "qui"

SELECT jdoc->'guid ', jdoc->'name ' FROM api WHERE jdoc> ' {"tags": ["qui"]} ';

This request can involve a simple GIN-index on a column jdoc. But note that such index will store copies of all keys and values in the field jdoc, whereas an index of expression from.
The previous example stores only the data in object with a key tags. Though the approach with a simple index much more floppy (as it supports requests on any key), indexes.
Specific expressions most likely there will be less and faster, than simple indexes.

https://postgrespro.ru/docs/postgresql/10/datatype-json