How to import this JSON data into a postgresql table?

Question

i have the following JSON:

{
   "ticket":{
      "ticket":"61086762bb68d90001148fe9",
      "timestamp":"2021-08-02T18:45:06.581-0300"
   },
   "serie":{
      "measurement":{
         "nemo":"RT",
         "description":"Real Time"
      },
      "tagSet":{
         "Granularity":"1h",
         "Facility":"382"
      },
      "fieldSet":{
         "EnvTemperature":"°C",
         "HorizIrrad":"W/m²"
      }
   },
   "values":[
      {
         "tagSet":{
            "Facility":"382",
            "Granularity":"1h"
         },
         "fieldSet":{
            "EnvTemperature":7.0,
            "HorizIrrad":632.0
         },
         "time":"2021-08-02T11:00:00.000-0300"
      },
      {
         "tagSet":{
            "Facility":"382",
            "Granularity":"1h"
         },
         "fieldSet":{
            "EnvTemperature":10.0,
            "HorizIrrad":884.0
         },
         "time":"2021-08-02T12:00:00.000-0300"
      }
   ]
}

And I want to know how could i import "EnvTemperature", "HorizIrrad" and "time" from the section "values" into a table like this:

time	EnvTemperature	HorizIrrad
2021-08-02 11:00:00	7	632
2021-08-02 12:00:00	10	884

or like this:

time	Fieldset	value
2021-08-02 11:00:00	EnvTemperature	7
2021-08-02 11:00:00	HorizIrrad	632
2021-08-02 12:00:00	EnvTemperature	10
2021-08-02 12:00:00	HorizIrrad	884

klin · Accepted Answer · 2021-08-03 02:13:53Z

1

Use the function jsonb_array_elements(jsonb) that returns all json array elements as value.

select 
    (value->>'time')::timestamp as "time",
    (value->'fieldSet'->>'EnvTemperature')::numeric as "EnvTemperature",
    (value->'fieldSet'->>'HorizIrrad')::numeric as "HorizIrrad"
from the_data
cross join jsonb_array_elements(json_col->'values')

Test it in Db<>fiddle.

Read about JSON Functions and Operators.

answered Aug 3, 2021 at 2:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to import this JSON data into a postgresql table?

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