Postgres and jsonb - search value at any key

Question

Is possible to look for a given value at any key in a JSONB column in Postgres? In the documentation I can't see any example.

Example value at a JSONB column:

{
  a: 1,
  b: 2,
  c: 3
}

I want to find all records that have 1 as a value anywhere. NOTE: there may be other keys than a, b, c unknown at the moment.

top level? or anywhere deep? – Vao Tsun May 02 '17 at 13:52 — Vao Tsun, May 02 '17 at 13:52
can you post an example of your jsonb text? – McNets May 02 '17 at 13:53 — McNets, May 02 '17 at 13:53

score 15 · Accepted Answer · answered May 02 '17 at 14:12

use value of jsonb_each_text, sample based on previous sample of McNets,:

t=# select * from json_test join jsonb_each_text(json_test.data) e on true 
where e.value = '1';
 id |                 data                 | key | value
----+--------------------------------------+-----+-------
  1 | {"a": 1}                             | a   | 1
  3 | {"a": 1, "b": {"c": "d", "e": true}} | a   | 1
(2 rows)

score 3 · Answer 2 · answered Oct 26 '20 at 13:32

3

Maybe late, but it helped me:

with my_data(id, jdata) as (
values
    (1, '{ "a": 4, "b": 5}'::jsonb),
    (2, '{ "a": 1, "b": 2}'::jsonb),
    (3, '{ "x": {"y": 3, "z": 1}}'::jsonb),
    (4, '{ "a": [2,1,4]}'::jsonb),
    (5, '{ "a": [{"x": 2}, {"y": 1}, {"z": 2}]}'::jsonb)
)

select id, jdata
from my_data
where jdata @? '$.** ? (@ == 1)';

 id |           jdata           
----+---------------------------
  2 | { "a": 1, "b": 2 }
  3 | { "x": { "y": 3, "z": 1 } }
  4 | { "a": [2, 1, 4] }
  5 | { "a": [ { "x": 2 }, { "y": 1 }, { "z": 2 } ] }
(4 rows)

answered Oct 26 '20 at 13:32

Julien

77
4

You should add, that this requires Postgres 12 as the SQL JSON/Path operators were introduced in that version – Oct 26 '20 at 13:36
1

It worked greatly but it is case sensitive. How to change to case insensitive? – Raphael Stefani Mar 25 '21 at 19:53
1

I found it: where jdata @? '$.** ? (@ like_regex 1 flag "i")' - https://stackoverflow.com/questions/42130740/postgresql-query-for-objects-in-nested-jsonb-field – Raphael Stefani Mar 25 '21 at 20:11

score 2 · Answer 3 · answered May 02 '17 at 14:15

Use json_each_text():

with my_data(id, jdata) as (
values
    (1, '{ "a": 1, "b": 2, "c": 3}'::json),
    (2, '{ "j": 4, "k": 5, "l": 6}'::json),
    (3, '{ "x": 1, "y": 2, "z": 3}'::json)
)

select id, jdata
from my_data,
lateral json_each_text(jdata) 
where value::int = 1

 id |           jdata           
----+---------------------------
  1 | { "a": 1, "b": 2, "c": 3}
  3 | { "x": 1, "y": 2, "z": 3}
(2 rows)

Postgres and jsonb - search value at any key

3 Answers3

Linked