Extract all values from a PostgreSQL JSON array given a key-CodePudding

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'

CodePudding user response：

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

CodePudding user response：

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