How to PSQL query for jsonb column with nested array to contain string

Question

I have a table that has a jsonb foo with below nested structure

foo: {"bar":[{"baz":["special string"]}]}

I want to query in psql for all records whose foo.bar can contain an object with the array value at baz containing the "special string".

The farthest I've gotten is below but it's not exactly comprehensive like I need it to be:

SELECT * 
FROM table
WHERE foo->'bar'->0->'baz' = '["special string"]';

Thank you.

Answer 1

You can use either the @> "contains structure" operator

SELECT * 
FROM table
WHERE foo @> '{"bar": [{"baz": ["special string"]}]}';

or a jsonpath with the @? operator:

SELECT * 
FROM table
WHERE foo @? 'strict $.bar[*] ? (@.baz[*] == "special string")';

Answer 2

If you're dealing with some variability in the structure of the jsonb values and you don't want to tolerate it, strict mode proposed by @Bergi will skip the mismatched ones. The patter using strict and @> example only matches four of the examples below (1,4,5,6) - which might be preferable.

SELECT * FROM my_table
WHERE foo @? 'lax $.bar[*].baz[*] ? (@ == "special string")';

If you do want to tolerate some variance, the default lax mode won't mind if you're not dealing with an array anywhere in the (sub)path you specified, or it will reach down a few array dimensions deeper on its own if it is an array, but more complex than you outlined. That way, you can catch nine examples (1,4,5,6,7,8,9,10,11) below - which might or might not be desired, but could be useful for other purposes.

It's possible to express the same rules in either mode - in lax you need additional filter expressions to narrow it down at the end, while in strict you need to explicitly handle alternative cases in filters on the spot, mid-path.

Here's a playground:

create table my_table(id, foo,comments) as values
 (0,'"special string"'::jsonb,'just the target string, no structure')
,(1,'{"bar":[{"baz":["special string"]}]}'::jsonb,'primary example')
,(2,'{"key1":[{"baz":["special string"]}]}','missing `bar`')
,(3,'{"bar":[{"key1":["special string"]}]}','missing `baz`')
,(4,'{"bar":[{"baz":["special string"]},
     {"key1":0}]}'
   ,'`key1` neighbouring `bar`')
,(5,'{"bar":[{"baz":["special string"]},
             {"key1":0}],
      "key2":0}'
   ,'`key1` replacing `baz` in 2nd element of `bar`, `key1` neighbouring `bar`')
,(6,'{"bar":[{"baz":["string1",
                     "special string"]},
             {"key1":0}],
      "key2":0}'
  ,'`key1` replacing `baz` in 2nd element of `bar`, 
    `key1` neighbouring `bar`, additional element in `baz`')
,(7,'{"bar":{"baz":"special string"}}'
   ,'neither `bar` nor `baz` are arrays')
,(8,'{"bar":[{"baz":"special string"}]}'
   ,'only `bar` is an array');

Plus, additional layers of arrays:

insert into my_table(id, foo,comments) values
 (9,'{"bar":[{"baz":[ ["s11","s12","special string"]
                     ,["s21","s22"]
                    ]
              }]}'
   ,'`baz` is 2d')
,(10,'{"bar":[{"baz":[ [ ["s111","s112"]
                        ,["s121","s122"] ]
                      ,[ ["s211","s212","special string"]
                        ,["s221","s222"] ]
                     ]
              }]}'
    ,'`baz` is 3d')
,(11,'{"bar":[{"baz":[ [ [ ["s1111","s1112"]
                          ,["s1121","s1122"] ]
                        ,[ ["s1211","s1212"]
                          ,["s1221","s1222"] ]
                       ]
                      ,[ [ ["s2111","s2112"]
                          ,["s2121","s2122"] ]
                        ,[ ["s2211","s2212","special string"]
                          ,["s2221","s2222"] ]
                       ]
                     ]
               }]}'
    ,'`baz` is 4d')
,(11,'{"bar":[[{"baz":["special string"]}]]}',    '`bar` is 2d')
,(12,'{"bar":[[[{"baz":["special string"]}]]]}',  '`bar` is 3d')
,(13,'{"bar":[[[[{"baz":["special string"]}]]]]}','`bar` is 4d');