CODEWITHSUNDEEP
jsonpostgresql
Aliaksei
Aliaksei

Reputation: 1457

Postgresql search in json

Foк example I have such table

                id                               data
0ab5203b-9157-4934-8aba-1512afb0abd0 {"title":"Board of Supervisors Meeting","id":"1i3Ytw1mw98"}
7ee33a18-63da-4432-8967-bde5a44347a0 {"title":"Board of Supervisors Meeting","id":"4-dNAg2mn6o"}
8d71ca35-74eb-4751-b635-114bf04843f1 {"title":"COPD 101", "id":"l9O0jCR-sxg"}

And selects

select * from module_data where data::json->>'title' like '%Board%' 
select * from module_data where data->>'title' like '%Board%'

Questions:

  1. How to search for any json node? Something like

    select * from module_data where data::json like '%Board%'

  2. JSON structure in the rows of the table can be different

I have a table audit

id
created
json

I need to find the row when the DTO object was changed

Upvotes: 0

Views: 56

Answers (1)

Dmitry Chirkin
Dmitry Chirkin

Reputation: 1084

PostgreSQL 10 introduces Full Text Search on JSONB

CREATE INDEX ON module_data
    USING gin ( to_tsvector('english', data) );

The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.

Upvotes: 1

Related Questions