Pointer to a JSON field with dynamic keys

Question

I'm on PG 9.5 and I have a table Visitors(id, data::json)

Example:

Visitor(id: 1, data: {name: 'Jack', age: 33, is_user: true })

I would like to fulfill such requests as

• Give me all visitors named Jack and age> 25
• Give me all the visitors who are users, but where the name is not indicated (the key is not in json)

The keys are inside the specified data column and, as such, are dynamic.

Which index makes the most sense in this situation?

Answer 1

You can use the GIN index on the jsonb column , which gives you generalized dynamic indexing of keys and values ​​inside a JSON value.

CREATE TABLE visitors (
  id integer,
  data jsonb
);

CREATE INDEX idx_visitors_data ON cards USING GIN (data);

SELECT * FROM visitors
WHERE data -> 'is_user' AND NOT data ? 'name';

, GIN . , 25 :

SELECT * FROM visitors
WHERE data @> '{"name": "Jack"}' AND ((data ->> 'age')::integer) > 25;

"" , , "", , 25, ,

, , GIN JSON, , , . , data -> 'age', :

CREATE INDEX idx_visitors_data_age ON visitors ( ((data ->> 'age')::integer) );

( , ).

. .

Answer 2

JsQuery - jsonb, jsonb ( PostgreSQL), ​​, . : https://github.com/postgrespro/jsquery.

jsonb_path_value_ops:

CREATE INDEX idx_visitors ON visitors USING GIN (jsonb jsonb_path_value_ops);

:

select * from visitors where jsonb @@ 'name = "Jack" and age > 25';
select * from visitors where jsonb @@ 'not name = * and is_user=true';