How to get a simple CSV text column from a multidimension array column in postgres?-CodePudding

This data is in a column called Triggers.

[{"item": "Pollution", "type": "Asthma trigger", "user_entered_item": false}, {"item": "Emotions", "type": "Asthma trigger", "user_entered_item": false}]

I want to end with a column called Triggers_Simple

Pollution, Emotions

So going form a MD array to a text column (CSV)

I am basically selecting all the values from the key "item" from the "Triggers" column.

CodePudding user response：

You can use jsonb_array_elements to iterate the array, ->> to access an object property and get back a text, and string_agg to join the strings together by a comma separator. Put the whole thing in a subquery:

SELECT (
  SELECT string_agg(value->>'item', ', ')
  FROM jsonb_array_elements(triggers)
) AS triggers_simple
FROM example;

^{(online demo)}