Expand Postgresql Nested Array Json Field

Question 1

I have a table (log_table) and in this table there is a nested array json field (activities). With using this activities field, I want to normalize my row.

log_table:

- id:long- activities:json- date:timestamp

example activities field:

[    {       "actionType":"NOTIFICATION",      "items":null   },   {       "actionType":"MUTATION",      "items":[          {             "id":387015007,            "name":"epic",            "value":{                "currency":"USD",               "amount":1.76            }         },         {             "id":386521039,            "name":"test",            "value":{                "currency":"USD",               "amount":1.76            }         }      ]   }]

As query, I've tried:

select * from log_table l, json_array_elements(l.activities) elems,json_array_elements(elems->'items') obj;

With this query, I got error like below:

ERROR:  cannot call json_array_elements on a scalar

Is there any suggestion?

Question 2

The lack ofitems should be marked as[null], notnull. You can use the case expression to correct this, e.g.:

select elems->>'actionType' as action_type, objfrom log_tablecross join jsonb_array_elements(l.activities::jsonb) elemscross join jsonb_array_elements(case elems->'items' when 'null' then '[null]' else elems->'items' end) obj action_type  |                                       obj--------------+--------------------------------------------------------------------------------- NOTIFICATION | null MUTATION     | {"id": 387015007, "name": "epic", "value": {"amount": 1.76, "currency": "USD"}} MUTATION     | {"id": 386521039, "name": "test", "value": {"amount": 1.76, "currency": "USD"}}(3 rows)

Question 3

Thanks for your answer; I also wonder something about it. How can I create index forlog->activities->actionTypes->id in here.

Question 4

There is no type of index that you could use here.

klin 123k15 gold badges240 silver badges262 bronze badges · Accepted Answer · 2020-02-08 19:55:32Z

The lack ofitems should be marked as[null], notnull. You can use the case expression to correct this, e.g.:

select elems->>'actionType' as action_type, objfrom log_tablecross join jsonb_array_elements(l.activities::jsonb) elemscross join jsonb_array_elements(case elems->'items' when 'null' then '[null]' else elems->'items' end) obj action_type  |                                       obj--------------+--------------------------------------------------------------------------------- NOTIFICATION | null MUTATION     | {"id": 387015007, "name": "epic", "value": {"amount": 1.76, "currency": "USD"}} MUTATION     | {"id": 386521039, "name": "test", "value": {"amount": 1.76, "currency": "USD"}}(3 rows)

Thanks for your answer; I also wonder something about it. How can I create index forlog->activities->actionTypes->id in here.

Movatterモバイル変換

Collectives™ on Stack Overflow

Expand Postgresql Nested Array Json Field

1 Answer1

2 Comments

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS