CODEWITHSUNDEEP
sqljsonpostgresql
adams
adams

Reputation: 301

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

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

Upvotes: 2

Views: 3011

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658102

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

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:

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

Upvotes: 0

klin
klin

Reputation: 121784

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)

Upvotes: 1

Related Questions