从给定键的PostgreSQL JSON数组中提取所有值

Question

如何在postgres中提取json

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

其中DSK值

它返回空值

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

Answer 1

您可以使用jsonb_path_query_array函数并从数组中提取整个值

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

DBfiddle

Answer 2

如前所述，您不能使用您的方法，因为它是一个数组，但您可以尝试使用json函数的不同方法:

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