sql - 如何在postgresql中逐行存储逗号分隔值

Question

输入:

('{"user":{"status":1,"loginid":1,"userids":{"userid":"5,6"}}}')  

我想像这样插入到我的表中:

userid    loginid    status  
---------------------------
5            1         1      
6            1         1

Answer 1

使用regexp_split_to_table().假设列是整数:

with input_data(data) as (
values
    ('{"user":{"status":1,"loginid":1,"userids":{"userid":"5,6"}}}'::json)
)

-- insert into my_table(userid, loginid, status)
select 
    regexp_split_to_table(data->'user'->'userids'->>'userid', ',')::int as userid,
    (data->'user'->>'loginid')::int as loginid,
    (data->'user'->>'status')::int as status
from input_data

 userid | loginid | status 
--------+---------+--------
      5 |       1 |      1
      6 |       1 |      1
(2 rows)