How to convert JSON data into table and store into Postgres DB using Node JS?

Question

I need help in converting json data into datatable and store inside postgresql DB.

For example,

[{"name":"abc", "role":"swe"}, {"name":"xyz", "role":"Tester"}]

I'd like to convert the above data or lets say store the data as a table with rows and columns.

I think we do have a datatype called "json" but it's to store the entire json right? But I want to convert as real table what we see in DB, like below,

   name   |  role       
--------------+-------
"abc"     | "swe"
"xyz"     | "Tester

Could you please help me how to parse them please? I'm using Node JS.

Any pointers will be helpful. Thanks a lot for your time.

Hello! It's hard to answer without knowing whether you have a DB set up already. Do you? Also, do you have any code which already interacts with your DB? If so it'll be easier to answer your question :) If not, the short answer is that you need to create an SQL query which does an INSERT operation using the values in that JSON array. — basicallydan
– basicallydan, Commented May 29, 2019 at 12:14

Paresh Barad · Accepted Answer · 2019-05-29 12:39:51Z

I think it will work, convert your json into object and prepare and insert it using npm package node-postgres(np) and node-sql(sql || sql string builder for node), Take reference from the following the code.

const pg = require('pg');
const sql = require('sql');

let usersToInsert = JSON.parse(`[{"name":"abc", "role":"swe"}, {"name":"xyz", "role":"Tester"}]`);

let User = sql.define({
  name: 'users',
  columns: [
    'name',
    'role'
  ]
});

async function run() {
  let client;
  try {
    client = new pg.Client({
      connectionString: 'postgresql://localhost/node_example'
    });
    await client.connect();
    let query = User.insert(usersToInsert).toQuery();
    console.log(query);
    let {rows} = await client.query(query);
    console.log(rows);
  } catch (e) {
    console.error(e);
  } finally {
    client.end();
  }
}

run();

Oto Shavadze · Accepted Answer · 2019-05-29 13:02:07Z

0

May be something like you need ?

select jsonb_array_elements(j)->>'name' as name, jsonb_array_elements(j)->>'role' as role 
from (
    select '[{"name":"abc", "role":"swe"}, {"name":"xyz", "role":"Tester"}]'::jsonb as j
) t

If so, You can simply do INSERT INTO .. SELECT for saving data into table

demo: https://rextester.com/LIV68809

edited May 29, 2019 at 13:02

answered May 29, 2019 at 12:43

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Collectives™ on Stack Overflow

How to convert JSON data into table and store into Postgres DB using Node JS?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related