Expand Postgresql Nested Array Json Field

Question

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?

klin · Accepted Answer · 2020-02-08 19:55:32Z

1

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

select elems->>'actionType' as action_type, obj
from log_table
cross join jsonb_array_elements(l.activities::jsonb) elems
cross 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)

answered Feb 8, 2020 at 19:55

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sha Over a year ago

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

klin Over a year ago

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

Collectives™ on Stack Overflow

Expand Postgresql Nested Array Json Field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related