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)
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 |