Postgresql from Json Object to array

Question

How can I convert a Postgresql stored json of this form

{"Kategorie": [{"ID": "environment", "ID": "economy"}]}

to get ["environment", "economy"] only using Postgresqls json flavoured syntax. The array in the stored source has here two elements, but may contain more (or only one). And the resulting array should result in all value elements.

Answer 1

This may give you something to work with:

SELECT ARRAY(select json_extract_path_text(x, 'ID') from
    json_array_elements(
        '{"Kategorie": [{"ID": "environment"}, {"ID": "economy"}]}'::json->'Kategorie') 
    as x)

The result is a text array:

{environment,economy}

It is entirely possible that there's a cleaner way to do this :)

The JSON operators documentation has the details. (This is 9.3+ only, 9.2 had very few utility functions.)