sql - 如何将我的 json 对象转换为 postgres 表

Question

我正在尝试将批量数据插入到 postgres 中。所以我已经将我的数据转换为 json 对象，现在我想将该数据插入到一个物理表中，json 对象可以包含超过 100k 条记录。所以我想通过使用 postgres 函数动态地完成它。我的问题是如何将我的样本数据查询为表格

这是示例 json 数据

[
  {
    "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
  }
]

Answer 1

您可以使用 json 列(例如 jsondata)创建表(例如 tab)并使用 json_array_elements()功能:

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