Creating table views with columns from nested value of a jsonb column

Question

I have a jsonb column in one of the tables that has the following structure:

{
  "3424": {
    "status": "pending",
    "remarks": "sample here"
  },
  "6436": {
    "status": "new",
    "remarks": "sample here"
  },,
  "9768": {
    "status": "cancelled",
    "remarks": null,
    "by": "customer"
  }
}

I am trying to create a view that will put the statuses in individual columns and the key will be their value:

pending | new  | cancelled | accepted | id | transaction
3424    | 6436 | 9768      | null     | 1  | testing

The problem is the key is dynamic (numeric and corresponds to some id) so I cannot pinpoint the exact key to use the functions/operations stated here: https://www.postgresql.org/docs/9.5/functions-json.html

I've read about json_path_query and was able to extract the statuses here without the need to know the key but I cannot combine it with the integer key yet.

select mt.id, mt.transaction, hstatus from mytable mt
cross join lateral jsonb_path_query(mt.hist, '$.**.status') hstatus
where mt.id = <id>

but this returns the statuses as rows for now. I'm pretty noob with (postgre)sql so I've only gotten this far.

What if you have two entries with pending? Or three with cancelled? — user330315
– user330315, Commented Jul 28, 2021 at 10:59

score 2 · Accepted Answer · 2021-09-18 17:25:03Z

You can indeed use a PATH query. Unfortunately it's not possible to access the "parent" inside a jsonpath in Postgres. But you can workaround that, by expanding the whole value to a list of key/values so that id value you have, can be accessed through .key

select jsonb_path_query_first(the_column, '$.keyvalue() ? (@.value.status == "pending").key') #>> '{}' as pending,
       jsonb_path_query_first(the_column, '$.keyvalue() ? (@.value.status == "new").key') #>> '{}' as new,
       jsonb_path_query_first(the_column, '$.keyvalue() ? (@.value.status == "cancelled").key') #>> '{}' as cancelled,
       jsonb_path_query_first(the_column, '$.keyvalue() ? (@.value.status == "accepted").key') #>> '{}' as accepted,
       id, 
       "transaction"
from the_table

The jsonpath function $.keyvalue() returns something like this:

{"id": 0, "key": "3424", "value": {"status": "pending", "remarks": "sample here"}}

This is then used to pick the an element through a condition on @.value.status and the accessor .key then returns the corresponding key value (e.g. 3424)

The #>> '{}' is a hack to convert the returned jsonb value into a proper text value (otherwise the result would be e.g. "3424" instead of just 3424.

Collectives™ on Stack Overflow

Creating table views with columns from nested value of a jsonb column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related