Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table example (
    id serial not null constraint example_pk primary key,
    data json not null);

INSERT INTO public.example (id, data) VALUES 
  (1, '[{"key": "1", "value": "val11"}, {"key": "2", "value": "val12"}]')
, (2, '[{"key": "1", "value": "val21"}]')
, (3, '[{"key": "1", "value": "val31"}, {"key": "2", "value": "val32"}]')
, (4, '[{"key": "2", "value": "val41"}, {"key": "2", "value": "val42"}]')
returning *;

CREATE TABLE

id
data

1
[{"key": "1", "value": "val11"}, {"key": "2", "value": "val12"}]

2
[{"key": "1", "value": "val21"}]

3
[{"key": "1", "value": "val31"}, {"key": "2", "value": "val32"}]

4
[{"key": "2", "value": "val41"}, {"key": "2", "value": "val42"}]

INSERT 0 4

--row 2 missing
SELECT id,
       jsonb_path_query(
                   data::jsonb,
                   '$[*] ? (@.key == "2").value'
               )::text AS values
FROM example;

id
values

1
"val12"

3
"val32"

4
"val41"

4
"val42"

SELECT 4

--equivalent of the above
SELECT id, values
FROM example, lateral jsonb_path_query(
                          data::jsonb,
                          '$[*] ? (@.key == "2").value'
                      ) AS v(values);

id
values

1
"val12"

3
"val32"

4
"val41"

4
"val42"

SELECT 4

--again, an equivalent, but using an explicit join
SELECT id,
       values
FROM example join
     jsonb_path_query(data::jsonb,
                      '$[*] ? (@.key == "2").value'
                     ) AS v(values)
     on true;

id
values

1
"val12"

3
"val32"

4
"val41"

4
"val42"

SELECT 4

--solved with a scalar function
SELECT id, jsonb_path_query_first(data::jsonb, '$[*]?(@.key=="2").value')
FROM example;

id
jsonb_path_query_first

1
"val12"

2
null

3
"val32"

4
"val41"

SELECT 4

--solved with a scalar subquery
SELECT id,(SELECT jsonb_path_query(data::jsonb,'$[*]?(@.key=="2").value')LIMIT 1)
FROM example;

id
jsonb_path_query

1
"val12"

2
null

3
"val32"

4
"val41"

SELECT 4

--solved with a more appropriate explicit join
SELECT id, values
FROM example LEFT JOIN jsonb_path_query(data::jsonb, '$[*]?(@.key=="2").value') 
                       AS srf(values) 
             ON true;

id
values

1
"val12"

2
null

3
"val32"

4
"val41"

4
"val42"

SELECT 5

id	data
1	[{"key": "1", "value": "val11"}, {"key": "2", "value": "val12"}]
2	[{"key": "1", "value": "val21"}]
3	[{"key": "1", "value": "val31"}, {"key": "2", "value": "val32"}]
4	[{"key": "2", "value": "val41"}, {"key": "2", "value": "val42"}]