Postgres 15

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

CREATE TABLE example (
  id serial NOT NULL PRIMARY KEY
, data jsonb NOT NULL               -- jsonb already!
);

-- note the added row 4 with multiple qualifying objects!
INSERT INTO example (id, data) VALUES 
  (1, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]')
, (2, '[{"key": "1", "value": "val1"}]')
, (3, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]')
, (4, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val4"}, {"key": "2", "value": "val44"}]')  -- duplicate key 2
RETURNING *;

CREATE TABLE




id
data


1
[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]

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

3
[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]

4
[{"key": "1", "value": "val1"}, {"key": "2", "value": "val4"}, {"key": "2", "value": "val44"}]



INSERT 0 4


-- original problem query
SELECT id
     , jsonb_path_query(data, '$[*] ? (@.key == "2").value')::VARCHAR AS values
FROM   example;


id
values


1
"val2"

3
"val2"

4
"val4"

4
"val44"



SELECT 4


-- first result
SELECT id
     , jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}' AS first_value
FROM   example;


id
first_value


1
val2

2
null

3
val2

4
val4



SELECT 4


-- all results as set
SELECT e.id
     , d.val #>> '{}' AS any_value
FROM   example e
LEFT   JOIN LATERAL jsonb_path_query(e.data, '$[*] ? (@.key == "2").value') d(val) ON true


id
any_value


1
val2

2
null

3
val2

4
val4

4
val44



SELECT 5


-- all results as JSON array
SELECT id
     , jsonb_path_query_array(data, '$[*] ? (@.key == "2").value') AS all_values
FROM   example;


id
all_values


1
["val2"]

2
[]

3
["val2"]

4
["val4", "val44"]



SELECT 4


-- helper function
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

CREATE FUNCTION


-- all results as text array
SELECT id
     , ARRAY(SELECT jsonb_array_elements_text(jsonb_path_query_array(data, '$[*] ? (@.key == "2").value'))) AS all_values_txt
     , jsonb_array_to_text_array(jsonb_path_query_array(data, '$[*] ? (@.key == "2").value')) AS all_values_txt2
FROM   example;


id
all_values_txt
all_values_txt2


1
{val2}
{val2}

2
{}
{}

3
{val2}
{val2}

4
{val4,val44}
{val4,val44}



SELECT 4

id	data
1	[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]
2	[{"key": "1", "value": "val1"}]
3	[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]
4	[{"key": "1", "value": "val1"}, {"key": "2", "value": "val4"}, {"key": "2", "value": "val44"}]