Search JSON Results for ID

Questions
21

Check the example below:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "url": "https://5ea797ea052c31e95a2806ff7e3c62d5.m.pipedream.net",
        "options": {}
      },
      "name": "HTTP Request2",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        480,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"Function\"].json[\"phone\"].match(/\\d+/g)[0]}}",
              "value2": "={{$item(0).$node[\"HTTP Request2\"].json[\"body\"][\"external_number\"].match(/\\d+/g)[0]}}"
            },
            {
              "value1": "={{$node[\"Function\"].json[\"mobile\"].match(/\\d+/g)[0]}}",
              "value2": "={{$item(0).$node[\"HTTP Request2\"].json[\"body\"][\"external_number\"].match(/\\d+/g)[0]}}"
            }
          ]
        },
        "combineOperation": "any"
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1140,
        300
      ]
    },
    {
      "parameters": {},
      "name": "NoOp",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1490,
        170
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      contacts: [\n         {\n        \"id\": 111111,\n        \"name\": \"Ben\",\n        \"address1\": \"\",\n        \"address2\": \"\",\n        \"city\": \"\",\n        \"state\": \"\",\n        \"zip\": \"\",\n        \"email\": \"[email protected]\",\n        \"phone\": \"\",\n        \"mobile\": \"\",\n        \"latitude\": null,\n        \"longitude\": null,\n        \"customer_id\": 123456789,\n        \"account_id\": 666,\n        \"notes\": \"\",\n        \"created_at\": \"2020-01-26T10:47:58.467-07:00\",\n        \"updated_at\": \"2020-01-26T10:48:07.234-07:00\",\n        \"vendor_id\": null,\n        \"properties\": {},\n        \"opt_out\": false,\n        \"extension\": \"\",\n        \"processed_phone\": \"\",\n        \"processed_mobile\": \"\",\n        \"ticket_matching_emails\": null\n      },\n      {\n        \"id\": 222222,\n        \"name\": \"M C\",\n        \"address1\": \"\",\n        \"address2\": null,\n        \"city\": \"\",\n        \"state\": \"\",\n        \"zip\": \"\",\n        \"email\": \"[email protected]\",\n        \"phone\": \"+12345556666\",\n        \"mobile\": \"+12345556667\",\n        \"latitude\": null,\n        \"longitude\": null,\n        \"customer_id\": 123456789,\n        \"account_id\": 666,\n        \"notes\": null,\n        \"created_at\": \"2020-05-14T13:25:40.835-06:00\",\n        \"updated_at\": \"2020-05-14T13:25:40.835-06:00\",\n        \"vendor_id\": null,\n        \"properties\": {},\n        \"opt_out\": false,\n        \"extension\": null,\n        \"processed_phone\": \"14031111234\",\n        \"processed_mobile\": \"14032221234\",\n        \"ticket_matching_emails\": null\n      },\n      {\n        \"id\": 333333,\n        \"name\": \"M C\",\n        \"address1\": \"\",\n        \"address2\": null,\n        \"city\": \"\",\n        \"state\": \"\",\n        \"zip\": \"\",\n        \"email\": \"[email protected]\",\n        \"phone\": \"+14431111234\",\n        \"mobile\": null,\n        \"latitude\": null,\n        \"longitude\": null,\n        \"customer_id\": 123456789,\n        \"account_id\": 666,\n        \"notes\": null,\n        \"created_at\": \"2020-01-27T17:02:12.005-07:00\",\n        \"updated_at\": \"2020-01-30T22:04:58.166-07:00\",\n        \"vendor_id\": null,\n        \"properties\": {},\n        \"opt_out\": false,\n        \"extension\": null,\n        \"processed_phone\": \"\",\n        \"processed_mobile\": null,\n        \"ticket_matching_emails\": null\n      },\n      {\n        \"id\": 444444,\n        \"name\": \"M T\",\n        \"address1\": \"\",\n        \"address2\": null,\n        \"city\": \"\",\n        \"state\": \"\",\n        \"zip\": \"\",\n        \"email\": \"[email protected]\",\n        \"phone\": \"+1 (555) 666-7777\",\n        \"mobile\": \"15557776666\",\n        \"latitude\": null,\n        \"longitude\": null,\n        \"customer_id\": 123456789,\n        \"account_id\": 666,\n        \"notes\": null,\n        \"created_at\": \"2020-05-14T13:25:11.213-06:00\",\n        \"updated_at\": \"2020-05-14T13:25:11.213-06:00\",\n        \"vendor_id\": null,\n        \"properties\": {},\n        \"opt_out\": false,\n        \"extension\": null,\n        \"processed_phone\": \"\",\n        \"processed_mobile\": \"\",\n        \"ticket_matching_emails\": null\n      },\n      {\n        \"id\": 555555,\n        \"name\": \"T I\",\n        \"address1\": null,\n        \"address2\": null,\n        \"city\": null,\n        \"state\": null,\n        \"zip\": null,\n        \"email\": \"[email protected]\",\n        \"phone\": \"+14567890200\",\n        \"mobile\": \"+14567782663\",\n        \"latitude\": null,\n        \"longitude\": null,\n        \"customer_id\": 123456789,\n        \"account_id\": 666,\n        \"notes\": null,\n        \"created_at\": \"2020-05-14T13:25:49.367-06:00\",\n        \"updated_at\": \"2020-05-14T13:25:49.367-06:00\",\n        \"vendor_id\": null,\n        \"properties\": {},\n        \"opt_out\": false,\n        \"extension\": null,\n        \"processed_phone\": \"\",\n        \"processed_mobile\": \"\",\n        \"ticket_matching_emails\": null\n      },\n      ]\n    }\n  }\n]\n"
      },
      "name": "Function2",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        720,
        300
      ],
      "notesInFlow": true,
      "notes": "Mockup get customers"
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.contacts.map(contact => ({ json: contact }));\n\n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        940,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "HTTP Request2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request2": {
      "main": [
        [
          {
            "node": "Function2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function2": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
22

Sorry Ricardo, was hoping to catch this before you replied. I just discovered there is another way for me to search against contacts, but it also searches against other things and returns results as a JSON table. This is potentially a good thing though as it would allow me to search once for both customers and contacts and then separate out both results. So what I’d want to do here is isolate “customers” and “contacts” so that I can then use that data in the workflow later. The catch here is that it’s possible for there to be a customer and a contact with a matching phone number (we eventually will be cleaning that up, but for now it’s possible). The example below shows that type of result. How I’d like this to work is:

  1. If there is a single contact, ignore the customer and just use the contact.
  2. If there is no contact or more than 1 contact, use the customer.
  3. If there is more than one customer, exit the workflow.
[
   {
      "quick_result":null,
      "results":[
         {
            "table":{
               "_id":111111,
               "_type":"customer",
               "_index":"customers",
               "_source":{
                  "table":{
                     "firstname":"Ben",
                     "lastname":"Solo",
                     "email":"[email protected]",
                     "business_name":"Not so Clever Admin",
                     "phones":[
                        {
                           "id":666666,
                           "label":"Mobile",
                           "number":"5554442222",
                           "customer_id":444444,
                           "created_at":"2021-04-05T13:40:25.322-06:00",
                           "updated_at":"2021-04-05T13:40:25.322-06:00",
                           "extension":""
                        }
                     ]
                  }
               }
            }
         },
         {
            "table":{
               "_id":122222,
               "_type":"contact",
               "_index":"contacts",
               "_source":{
                  "table":{
                     "customer_id":555555,
                     "firstname":"Right",
                     "lastname":"Guy",
                     "email":"",
                     "business_name":"Clever Admin",
                     "phones":[
                        "5554442222",
                        ""
                     ]
                  }
               }
            }
         }
      ],
      "error":null
   }
]
23

I had something written in Javascript that rewrites the JSON the way I need it. Any suggestions on getting that into n8n? As is, it doesn’t work in n8n but does work in Javascript. And obviously I intend on pointing const json to the webhook node that get’s the source JSON. Thanks again for all your help.

const json = '{"quick_result":null,"results":[{"table":{"_id":23462895,"_type":"customer","_index":"customers","_source":{"table":{"firstname":"Benjamin","lastname":"Solo","email":"[email protected]","business_name":"Not so Clever Admin","phones":[{"id":22570861,"label":"Mobile","number":"2225556922","customer_id":23462895,"created_at":"2021-04-05T13:40:25.322-06:00","updated_at":"2021-04-05T13:40:25.322-06:00","extension":""}]}}}},{"table":{"_id":23469053,"_type":"customer","_index":"customers","_source":{"table":{"firstname":"Dan","lastname":"Solo","email":null,"business_name":"Solo Killers","phones":[{"id":22576437,"label":"Mobile","number":"2225556922","customer_id":23469053,"created_at":"2021-04-05T22:24:07.258-06:00","updated_at":"2021-04-05T22:24:07.258-06:00","extension":""}]}}}},{"table":{"_id":1339643,"_type":"contact","_index":"contacts","_source":{"table":{"customer_id":23462895,"firstname":"John","lastname":"Solo","email":"","business_name":"Not so Clever Admin","phones":["2225556922",""]}}}},{"table":{"_id":1335594,"_type":"contact","_index":"contacts","_source":{"table":{"customer_id":23462918,"firstname":"Right","lastname":"Guy","email":"","business_name":"Clever Admin","phones":["2225556922",""]}}}}],"error":null}';
const data = JSON.parse(json);

function getData(data,type) {
	const res = [];
  for(let row of data.results) {
    if(row.table._index == type) {
   	let kk = row.table._source.table;
      kk['id'] = row.table._id;
      res.push(kk);        
       }
  }
  return res;
}

const customers = getData(data,'customers');
console.log(customers);
24

Hey @cleveradmin,

This is because you’re not returning an item. I made minor changes to your code snippet. The code now returns the item as expected by n8n.

const json = '{"quick_result":null,"results":[{"table":{"_id":23462895,"_type":"customer","_index":"customers","_source":{"table":{"firstname":"Benjamin","lastname":"Solo","email":"[email protected]","business_name":"Not so Clever Admin","phones":[{"id":22570861,"label":"Mobile","number":"2225556922","customer_id":23462895,"created_at":"2021-04-05T13:40:25.322-06:00","updated_at":"2021-04-05T13:40:25.322-06:00","extension":""}]}}}},{"table":{"_id":23469053,"_type":"customer","_index":"customers","_source":{"table":{"firstname":"Dan","lastname":"Solo","email":null,"business_name":"Solo Killers","phones":[{"id":22576437,"label":"Mobile","number":"2225556922","customer_id":23469053,"created_at":"2021-04-05T22:24:07.258-06:00","updated_at":"2021-04-05T22:24:07.258-06:00","extension":""}]}}}},{"table":{"_id":1339643,"_type":"contact","_index":"contacts","_source":{"table":{"customer_id":23462895,"firstname":"John","lastname":"Solo","email":"","business_name":"Not so Clever Admin","phones":["2225556922",""]}}}},{"table":{"_id":1335594,"_type":"contact","_index":"contacts","_source":{"table":{"customer_id":23462918,"firstname":"Right","lastname":"Guy","email":"","business_name":"Clever Admin","phones":["2225556922",""]}}}}],"error":null}';
const data = JSON.parse(json);

function getData(data,type) {
	const res = [];
  for(let row of data.results) {
    if(row.table._index == type) {
   	let kk = row.table._source.table;
      kk['id'] = row.table._id;
      res.push({json:kk});        
       }
  }
  return res;
}

return getData(data,'customers');

Hope this helps :slight_smile:

25

Yes, that appears to work. Now I just need to have const json bring in the HTTP request JSON. Essentially something like this, but that actually works.

const json = $node["Function2"].json["results"];