Split Array of JSON to Multiple Row


#1

I have a table that store information in JSON format (sample data below). Is it possible to make a query to split this into multiples row?

[{“score”:50,“name”:“BANK-MUTATION”,“rule”:“BANK-MUTATION”,“weight”:1,“messages”:[]},{“score”:50,“name”:“EMAIL”,“rule”:“EMAIL”,“weight”:1,“messages”:[]},{“score”:100,“name”:“APPLICATION-FILTER”,“rule”:“APPLICATION-FILTER”,“weight”:1,“messages”:[]},{“score”:50,“name”:“THIRD-PARTY-VALIDATION”,“rule”:“THIRD-PARTY-VALIDATION”,“weight”:1,“messages”:[]},{“score”:0,“name”:“NEW-PHONE”,“rule”:“NEW-PHONE”,“weight”:1,“messages”:[“AU02 - Jumlah SMS kurang dari 20”]},{“score”:50,“name”:“DUPLICATE_ID_NUMBER”,“rule”:“DUPLICATE_ID_NUMBER”,“weight”:1,“messages”:[]}]


#2

What database are you using?


#3

We are using postgresql


#4

Hello marvello,

You should use the ->, ->>, functionalities made for navigating inside json data.
See PostgreSQL documentation