Postgres json to view-CodePudding

I have a table like this (with an jsonb column): https://dbfiddle.uk/lGuHdHEJ

If I load this json with python into a dataframe:

import pandas as pd
import json

data={

    "id": [1, 2],
    "myyear": [2016, 2017],
    "value": [5, 9]

}

data=json.dumps(data)

df=pd.read_json(data)

print (df)

I get this result:

   id  myyear  value
0   1    2016      5
1   2    2017      9

How can a get this result directly from the json column via sql in a postgres view?

CodePudding user response：

Note: This assumes that your id, my_year, and value array are consistent and have the same length.

This answer uses PostgresSQL's json_array_elements_text function to explode array elements to the rows.

select jsonb_array_elements_text(payload -> 'id')      as "id",
       jsonb_array_elements_text(payload -> 'bv_year') as "myyear",
       jsonb_array_elements_text(payload -> 'value')   as "value"
from main

And this gives the below output,

id  myyear value
1   2016    5
2   2017    9

Although this is not the best design to store the properties in jsonb object and could lead to data inconsistencies later. If it's in your control I would suggest storing the data where each property's mapping is clear. Some suggestions,

You can instead have separate columns for each property.
If you want to store it as jsonb only then consider [{id: "", "year": "", "value": ""}]