Project an array in jsonb-CodePudding

Suppose we have this table:

CREATE TEMPORARY TABLE TEST (FIELD jsonb);
INSERT INTO TEST VALUES ('[{"key": 1},{"key": 2}]');
SELECT field FROM TEST;

Result:

field
[{"key": 1}, {"key": 2}]

I would like to query the table in order to obtain this result:

field
[1, 2]

that is, to project every element in the array respect to their key.

Can't find a simple way to do that.

CodePudding user response：

You can use a JSON path query:

SELECT jsonb_path_query_array(field, '$[*].key') 
FROM test;

[*] iterates over all values in the array and .key then returns the value for each key.