Parse json to string

Questions
1

how do I turn a json into a string?

json:
[

{

"id": 123123123 ,

"birth_date": 4947372010 ,

"cohort": "default" ,

"created_at": 1566858506 ,

"email": "[email protected]" ,

"first_name": "Alberto" ,

"last_name": "Saulo ferreira20" ,

"phone": 5548988087118 ,

"source": "app" ,

"unsubscribed": "true"

} ,

{

"id": 12345611 ,

"birth_date": 4947372010 ,

"cohort": "default" ,

"created_at": 1566858506 ,

"email": "[email protected]" ,

"first_name": "Alberto2" ,

"last_name": "Saulo ferreira3" ,

"phone": 5548988087118 ,

"source": "app" ,

"unsubscribed": "true"

} ,

{

"id": 123456789 ,

"birth_date": 4947372010 ,

"cohort": "default" ,

"created_at": 1566858506 ,

"email": "[email protected]" ,

"first_name": "Alberto3" ,

"last_name": "Saulo ferreira1" ,

"phone": 5548988087118 ,

"source": "app" ,

"unsubscribed": "true"

}

]

return:

INSERT table ( 
  id, 
  email, 
  first_name, 
  last_name, 
  phone, 
  unsubscribed ) 
  VALUES ( 
      '123123123', 
      '[email protected]', 
      'Alberto',
      'Saulo ferreira20',
      '5548988087118',
      'true'
 )

INSERT table ( 
  id, 
  email, 
  first_name, 
  last_name, 
  phone, 
  unsubscribed ) 
  VALUES ( 
      '123123123', 
      '[email protected]', 
      'Alberto',
      'Saulo ferreira20',
      '5548988087118',
      'true'
 )

INSERT table ( 
  id, 
  email, 
  first_name, 
  last_name, 
  phone, 
  unsubscribed ) 
  VALUES ( 
      '123123123', 
      '[email protected]', 
      'Alberto',
 ...



{
  "nodes": [
    {
      "parameters": {
        "functionCode": "\nreturn ([\n  {json: {\n   \"id\": 123123123,\n   \"birth_date\": 4947372010,\n   \"cohort\":\"default\",\n   \"created_at\":1566858506,\n   \"email\":\"[email protected]\",\n   \"first_name\":\"Alberto\",\n   \"last_name\":\"Saulo ferreira20\",\n   \"phone\":5548988087118,\n   \"source\":\"app\",\n   \"unsubscribed\":\"true\"\n  }},\n  {json: {\n   \"id\":12345611,\n   \"birth_date\":4947372010,\n   \"cohort\":\"default\",\n   \"created_at\":1566858506,\n   \"email\":\"[email protected]\",\n   \"first_name\":\"Alberto2\",\n   \"last_name\":\"Saulo ferreira3\",\n   \"phone\":5548988087118,\n   \"source\":\"app\",\n   \"unsubscribed\":\"true\"\n  }},\n  {json: {\n   \"id\": 123456789,\n   \"birth_date\":4947372010,\n   \"cohort\":\"default\",\n   \"created_at\":1566858506,\n   \"email\":\"[email protected]\",\n   \"first_name\":\"Alberto3\",\n   \"last_name\":\"Saulo ferreira1\",\n   \"phone\":5548988087118,\n   \"source\":\"app\",\n   \"unsubscribed\":\"true\"\n  }}\n]);"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -490,
        220
      ]
    },
    {
      "parameters": {
        "functionCode": "return `INSERT RPA.DBO.[CUSTOMER.IO_TMP] ( \n  id, \n  email, \n  first_name, \n  last_name, \n  phone, \n  unsubscribed ) \n  VALUES ( \n      '${item.id}', \n      '${item.email}', \n      '${item.first_name}',\n      '${item.last_name}',\n      '${item.phone}',\n      '${item.unsubscribed}'\n )`\n"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        -180,
        220
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "={{$node[\"FunctionItem\"].json}}"
      },
      "name": "Microsoft SQL",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        120,
        220
      ],
      "credentials": {
        "microsoftSql": "SQL Marche (DC)"
      }
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "FunctionItem",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "FunctionItem": {
      "main": [
        [
          {
            "node": "Microsoft SQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2

Hey @leosantosx!

Did you try using the JSON.stringify method? It will convert the JSON to string.

3

I want to return a query to be able to run it in microsoft sql

4

Okay. Sorry, I understood it incorrectly. Here’s a workflow that might help

You can also insert new values using the Insert operation. You will need the Set node to map the values to the correct column name. You can refer to the docs here: MySQL | Docs

5

the MicrosoftSQL node does not loop
only the first element of Json is seen

6

Did you try executing the workflow? The looping is handled by n8n. In the Expression Editor you will only see one output, but on when executing, n8n will run the query for all the items.

7
8

Thank you for pointing this out. You can use the Split in Batches node, create a batch of one, and connect the Output of the Microsoft SQL node to the Split In Batches node. Let me know if you need more help :slight_smile:

1 Like