Postgresql - Extract values from json from [] values

Question

In postgresql I am trying to query a view where one column called codeids is of type jsonb that looks like this - ["Code-oyg0vYNpL", "Code-m9db_s", "Code89nb"]. I want to query this column with the results being returned one value per line. In the example above the query should return 3 lines.

I have run queries on fully formed json blobs using json_array_elements and jsonb_array_length to extract parts of the json structure. But someone this simpler json structure is confusing me as I can't figure out the right format for postgresql statement to extract these three values. Thanks in advance.

SELECT
  role -> 'title' AS team_role,
  jsonb_array_length(role -> 'names') AS member_count
  FROM jsonb_array_elements(value -> 'team') AS team(role)

GMB · Accepted Answer · 2020-04-01 23:24:24Z

You are almost there... but you need to bring the actual table (or view) in the query. I find that the LATERAL JOIN syntax makes it more obvious here:

SELECT
    t.role -> 'title' AS team_role,
    jsonb_array_length(t.role -> 'names') AS member_count
FROM myview v
CROSS JOIN LATERAL jsonb_array_elements(v.codeids -> 'team') AS t(role)

Edit: if you are storing the jsonb array in a table column, that's a bit simpler:

create table test_table (codeids jsonb);
insert into test_table(codeids) values ('["Code-oyg0vYNpL", "Code-m9db_s", "Code89nb"]');

select x.role
from test_table t
cross join lateral jsonb_array_elements(t.codeids) x(role);

| role           |
| -------------- |
| Code-oyg0vYNpL |
| Code-m9db_s    |
| Code89nb       |

Collectives™ on Stack Overflow

Postgresql - Extract values from json from [] values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related