CODEWITHSUNDEEP
jsonpostgresql
AleksRous
AleksRous

Reputation: 51

Extract all values from a PostgreSQL JSON array given a key

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'

Upvotes: 1

Views: 643

Answers (2)

emomaliev
emomaliev

Reputation: 2383

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

Upvotes: 2

Renato
Renato

Reputation: 2157

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

Upvotes: 1

Related Questions