Postgres jsonb. Heterogenous json fields

Question

If I have a table with a single jsonb column and the table has data like this:

 [{"body": {"project-id": "111"}},
  {"body": {"my-org.project-id": "222"}},
  {"body": {"other-org.project-id": "333"}}]

Basically it stores project-id differently for different rows. Now I need a query where the data->'body'->'etc'., from different rows would coalesce into a single field 'project-id', how can I do that?

e.g.: if I do something like this:

select data->'body'->'project-id' projectid from mytable

it will return something like:

| projectid |
|       111 |

But I also want project-id's in other rows too, but I don't want additional columns in the results. i.e, I want this:

| projectid |
|       111 |
|       222 |
|       333 |

Are you saying you've stored the entire array as a jsonb value in one column of some row, or does this actually represent three rows that each have an object jsonb value? Please provide an SQL statement that sets up an example dataset. — Bergi
– Bergi, Commented Feb 12, 2020 at 23:09

GMB · Accepted Answer · 2020-02-13 00:33:57Z

I understand that each of your rows contains a json object, with a nested object whose key varies over rows, and whose value you want to acquire.

Assuming the 'body' always has a single key, you could do:

select jsonb_extract_path_text(t.js -> 'body', x.k) projectid
from t
cross join lateral jsonb_object_keys(t.js -> 'body') as x(k)

The lateral join on jsonb_object_keys() extracts all keys in the object as rows. Then we use jsonb_extract_path_text() to get the corresponding value.

Demo on DB Fiddle:

with t as ( 
  select '{"body": {"project-id": "111"}}'::jsonb js
  union all select '{"body": {"my-org.project-id": "222"}}'::jsonb
  union all select '{"body": {"other-org.project-id": "333"}}'::jsonb
)
select jsonb_extract_path_text(t.js -> 'body', x.k) projectid
from t
cross join lateral jsonb_object_keys(t.js -> 'body') as x(k)

| projectid  |
| :--------- |
| 111        |
| 222        |
| 333        |

Collectives™ on Stack Overflow

Postgres jsonb. Heterogenous json fields

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related