How can I convert my json object into postgres table

Question

I am trying to insert bulk data into postgres. So I have converted my data into json object and now I want to insert that data into a physical table the json object can contain over 100k records. So I want to do it dynamically by using postgres function. My question is how can I query my sample data as a table

This is the sample json data

[
  {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  },
  {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  },
 {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  }
]

Can you try this one, https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql — Zain Ul Abideen, Oct 04 '19 at 04:35

Barbaros Özhan · Accepted Answer · 2019-10-04T05:26:55.137

You can use create a table (e.g.tab) with json column (e.g.jsondata) and by using json_array_elements() function :

create table tab as
with tab as
(
 select '[
  {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  },
  {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  },
 {
    "sequence": 123123.0,
    "Card number ": "12312qwe",
    "Tracking number": 1231233.0,
    "Expiry Date": 43741.0
  }
]'::json as jsondata
)
select js ->> 'sequence' as sequence, js ->> 'Card number ' as Cardnumber, 
       js ->> 'Tracking number' as Trackingnumber, js ->> 'Expiry Date' as ExpiryDate
  from
  (
    select json_array_elements(jsondata) as js
      from tab
  ) q1

Demo

How can I convert my json object into postgres table

1 Answers1