How to store comma-separated values row by row in postgresql

Question

Input:

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

I want to insert into my table like this:

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

What language are you using on the app side? Typically you would be using a JSON library which can map to a record. — Tim Biegeleisen, Jan 04 '19 at 13:21
Ideally if "userid" can contain multiple ids, it would be an array rather than a string value as you have. So now you need to do your own processing of that value (use one of PG's split function to split by comma), then you would insert a row for each split result. — 404, Jan 04 '19 at 13:30

score 1 · Accepted Answer · answered Jan 04 '19 at 14:12

Use regexp_split_to_table(). Assuming that the columns are integers:

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)

score 0 · Answer 2 · answered Jan 04 '19 at 17:07

Would be simpler with an array (JSON array) to begin with. The you can use json_array_elements_text(json). See:

How to turn json array into postgres array?

Convert the list you have to an array with string_to_array(). Then unnest().

SELECT unnest(string_to_array(js#>>'{user,userids,userid}', ',')) AS userid
     , (js#>>'{user,loginid}')::int AS loginid
     , (js#>>'{user,status}')::int  AS status
FROM  (
   SELECT json '{"user":{"status":1,"loginid":1,"userids":{"userid":"5,6"}}}'
   ) i(js);

db<>fiddle here

I advise Postgres 10 or later for the simple form with unnest() in the SELECT list. See:

What is the expected behaviour for multiple set-returning functions in select clause?

I avoid regexp functions for simple tasks. Those are powerful, but substantially more expensive.

How to store comma-separated values row by row in postgresql

2 Answers2