Extract all values from a PostgreSQL JSON array given a key

Question

How exctract from json in postgres

[{"val":"2","dsk:"one"},{"val":"2","dsk":"two"},{"val":"3","dsk":"three"}]

where dsk values

It return null values

SELECT '[{"val":"2","dsk:"one"},{"val":"2","dsk":"two"},{"val":"3","dsk":"three"}]'::json->'dsk'

Because it's an array; you could get a single element with something like this SELECT '[{"val":"2","dsk:"one"},{"val":"2","dsk":"two"},{"val":"3","dsk":"three"}]'::json->0->'dsk' — Renato
– Renato, Commented Apr 30, 2022 at 13:56

emomaliev · Accepted Answer · 2022-04-30 14:21:42Z

2

You can use the jsonb_path_query_array function and extract the entire value from the array

select jsonb_path_query_array('[{"val":"2","dsk":"one"},{"val":"2","dsk":"two"},{"val":"3","dsk":"three"}]','$[*].dsk')

Demo in DBfiddle

answered Apr 30, 2022 at 14:21

emomaliev

2,4233 gold badges12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Renato · Accepted Answer · 2022-04-30 14:07:08Z

1

As mentioned you cannot use your approach because it is an array, but you can try a different one with a json function:

WITH data
AS (
    SELECT *
    FROM json_array_elements('[{"val":"2","dsk":"one"},{"val":"2","dsk":"two"},{"val":"3","dsk":"three"}]'::json)
    )
SELECT value->'dsk'
FROM data

answered Apr 30, 2022 at 14:07

Renato

2,1771 gold badge13 silver badges25 bronze badges

Collectives™ on Stack Overflow

Extract all values from a PostgreSQL JSON array given a key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related