How to insert multiple rows from JSON structure to MySQL column

Question

I need to insert multiple json value into a mysql column. Here I have a table named client

query = ' INSERT INTO client (user_id,client_id,tenant_id) values ?'
let queryClient = []
queryClient.push(clientData.user_id)
queryClient.push(clientData.client_id);
queryClient.push(clientData.tenant_id);

Before that I assigned req.body into clientData

Here is the connection to the mysql database

var queryMapClientIn = mysql.format(query, [queryClient])
const newMapClient = await conPool.query(queryMapClientIn, function (err, result) {
  if (err) throw err;
  console.log("Number of records inserted: " + result.affectedRows);
});

This is the code and json data is:

{
    "user_id" : 101,
    "client_id": 999,
    "tenant_id" : 1,
    "additionalClient" : [
        {"user_id" : 130},
        {"user_id" : 131}
    ]
}

Here is the json data and i need to push multiple user_id into user_id column on sql table. Can I get to know how to insert multiple user_id on this table.

Barmar · Accepted Answer · 2020-10-31 08:29:00Z

1

You can create a 2-dimensional array to insert multiple rows.

let clientData = {
    "user_id" : 101,
    "client_id": 999,
    "tenant_id" : 1,
    "additionalClient" : [
        {"user_id" : 130},
        {"user_id" : 131}
    ]
};
let queryClient = [[clientData.user_id, clientData.client_id, clientData.tenant_id]];
if (clientData.additionalClient) {
    let additionalClients = clientData.additionalClient.map(({user_id}) => [user_id, clientData.client_id, clientData.tenant_id]);
    queryClient = queryClient.concat(additionalClients);
}

let query = 'INSERT INTO client (user_id,client_id,tenant_id) values ?';
const newMapClient = await conPool.query(query, queryClient, function (err, result) {
  if (err) throw err;
  console.log("Number of records inserted: " + result.affectedRows);
});

edited Oct 31, 2020 at 8:29

answered Oct 31, 2020 at 8:07

Barmar

789k57 gold badges554 silver badges668 bronze badges

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

9 Comments

Rahman Haroon Over a year ago

Its showing map is map is undefined and it cannot read the property. Can I know what is the use of map here

Barmar Over a year ago

developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…

Barmar Over a year ago

That means that there's no clientData.additionalClient property like you showed.

Barmar Over a year ago

Read it carefully. It doesn't say that map is undefined, it says that clientData.additionalClient is undefined, since that's what we're trying to access the map property of.

Barmar Over a year ago

additional_client != additionalClient

|

Collectives™ on Stack Overflow

How to insert multiple rows from JSON structure to MySQL column

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related