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

Question

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?

Is your json structure constant or varying ? Postgres has json related functions which can work on json directly within a Postgres function call and constructing a query won't be needed. — Kaushik Nayak
– Kaushik Nayak, Commented Jun 27, 2019 at 4:46

Kaushik Nayak · Accepted Answer · 2019-06-27 05:14:55Z

2

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

answered Jun 27, 2019 at 5:14

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tenusha Guruge · Accepted Answer · 2019-06-27 05:09:24Z

1

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

answered Jun 27, 2019 at 5:09

Tenusha Guruge

2,3503 gold badges21 silver badges39 bronze badges

2 Comments

user11689312 Over a year ago

is this faster than inserting multirow values?

Tenusha Guruge Over a year ago

@niga I don't think there will be a large difference

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related