Extract Inner Item from Array

Hi,

I am getting this response from my SQL node:

What is the quickest way to extract only the pink highlighted part of the response?

This is my sample select:

SELECT
  'ABC Company Limited' [FullyQualifiedName],
  '+88 8888 8888'      [PrimaryPhone.FreeFormNumber],
  'Address Line 1'     [BillAddr.Line1],
  'Address Line 2'     [BillAddr.Line2],
  'Suburb'             [BillAddr.Suburb],
  'City'               [BillAddr.City],
  '8888'               [BillAddr.PostCode]
FOR JSON PATH, ROOT('customer');

Any help will be greatly appreciated.

Hi @harshil1712, any chance you could assist me with this?

Hey @Suratwala,

Can you paste that output here? If you click on the </> icon it will give you some space to put it so the formatting is correct. We can then have a play and see what we can do.

Hi Jon,

Here is the node:

and output:

[
{
"JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "{"customer":[{"FullyQualifiedName":"ABC Company Limited","PrimaryPhone":{"FreeFormNumber":"+88 8888 8888"},"BillAddr":{"Line1":"Address Line 1","Line2":"Address Line 2","Suburb":"Suburb","City":"City","PostCode":"8888"}}]}"
}
]

Thank you for your assistance.

Hey @Suratwala,

It isn’t pretty but here is one option you could use, The first node mocks the output you get from SQL then with a set node I convert the straing to a json object with JSON.parse() then in the ItemList node I just extract the part you want using the output variable from the set node along with customer.

There is going to be a few ways to do this but this is the first and quickest I thought of :slight_smile:

1 Like

Thanks @jon , that did the trick.

1 Like