CODEWITHSUNDEEP
sqlnode.jspostgresql
user11689312
user11689312

Reputation:

node.js - how to insert values from a json array into a sql database

I have incoming json structure like

{
    "type":1,
    "location":[
        {"lattitude":"0", "longitude":"0"},
        {"lattitude":"0", "longitude":"0"},
        {"lattitude":"0", "longitude":"0"}]
}

I need to insert this into a database like

|------|-----------|-----------|
| type | lattitude | longitude |
|------|-----------|-----------|
| 1    | 0         | 0         |
|------|-----------|-----------|
| 1    | 0         | 0         |
|------|-----------|-----------|
| 1    | 0         | 0         |
|------|-----------|-----------|

how do I parse the json and build an sql query?

Upvotes: 1

Views: 745

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

If you want to use Postgres solution, you may do

--INSERT INTO yourtab( type,lattitude,longitude)

select jsoncol->>'type' , j->>'lattitude'
             j->>'longitude'
 from
 ( values (:yourjsonstr :: jsonb ) ) as t(jsoncol) cross join 
          lateral jsonb_array_elements(jsoncol->'location')
                                   as j;

DEMO

Upvotes: 2

Tenusha Guruge
Tenusha Guruge

Reputation: 2290

You can use json-sql

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'users',
    values: {
        name: 'John',
        lastname: 'Snow',
        age: 24,
        gender: 'male'
    }
});

sql.query
// insert into users (name, lastname, age, gender) values ($p1, $p2, 24, $p3);

sql.values
// { p1: 'John', p2: 'Snow', p3: 'male' }

See the documentation: https://www.npmjs.com/package/json-sql

Upvotes: 1

Related Questions