Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
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');

SELECT 9



INSERT 0 6


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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`



SELECT 4


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example



SELECT 1


SELECT * 
FROM my_table
WHERE foo @@ 'strict $.bar[*].baz[*] == "special string"';


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example



SELECT 1


SELECT * 
FROM my_table
WHERE foo @@ 'strict "special string" == $.bar[*].baz[0]';


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example



SELECT 1


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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

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": ["special string"]}]]}
`bar` is 2d



SELECT 9


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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

9
{"bar": [{"baz": [["s11", "s12", "special string"], ["s21", "s22"]]}]}
`baz` is 2d

11
{"bar": [[{"baz": ["special string"]}]]}
`bar` is 2d



SELECT 8


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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

9
{"bar": [{"baz": [["s11", "s12", "special string"], ["s21", "s22"]]}]}
`baz` is 2d



SELECT 7


SELECT * 
FROM my_table
WHERE foo @@ '$.bar.baz == "special string"';


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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



SELECT 6


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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`



SELECT 4


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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`



SELECT 4


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


id
foo
comments


1
{"bar": [{"baz": ["special string"]}]}
primary example

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`



SELECT 4
id	foo	comments
1	{"bar": [{"baz": ["special string"]}]}	primary example
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`