Unnest json string array

Question

I'm using psql and I have a table that looks like this:

id | dashboard_settings
-----------------------
 1 | {"query": {"year_end": 2018, "year_start": 2015, "category": ["123"]}}

There are numerous rows, but for every row the "category" value is an array with one integer (in string format).

Is there a way I can 'unpackage' the category object? So that it just has 123 as an integer?

I've tried this but had no success:

SELECT jsonb_extract_path_text(dashboard_settings->'query', 'category') from table

This returns:

jsonb_extract_path_text | ["123"]

when I want:

jsonb_extract_path_text | 123

user330315 · Accepted Answer · 2019-12-10 15:45:03Z

1

You need to use the array access operator for which is simply ->> followed by the array index:

select jsonb_extract_path(dashboard_settings->'query', 'category') ->> 0
from the_table

alternatively:

select dashboard_settings -> 'query' -> 'category' ->> 0
from the_table

answered Dec 10, 2019 at 15:45

user330315

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2019-12-10 15:45:25Z

1

Consider:

select dashboard_settings->'query'->'category'->>0 c from mytable

| c   |
| :-- |
| 123 |

answered Dec 10, 2019 at 15:42

GMB

224k25 gold badges103 silver badges151 bronze badges