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, stackoverflow.com/questions/39224382/… — Zain Ul Abideen
– Zain Ul Abideen, Commented Oct 4, 2019 at 4:35

Barbaros Özhan · Accepted Answer · 2019-10-04 05:26:55Z

1

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

edited Oct 4, 2019 at 5:26

answered Oct 4, 2019 at 5:18

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

How can I convert my json object into postgres table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related