2

I'm looking to use SQL, in an Automation, to parse JSON data which is a JSON Array. I have 5M+ rows where I plan to parse the JSON data and write it to another DE where each id value is a separate row.

{
"items": [
{
"id": "dfa8eb26632ad060b978212abac639ad"
},
{
"id": "b523b2eb027edadad6fab9d5c265809f"
},
{
"id": "becb9c343c8741b4f9d47427a0edb024"
}
]
}

I've tried to use JSON_QUERY and have managed to get to the point where I've removed the array

SELECT JSON_QUERY(@jsonData,'$.items') as itemList from tableName

result

{
"id": "dfa8eb26632ad060b978212abac639ad"
},
{
"id": "b523b2eb027edadad6fab9d5c265809f"
},
{
"id": "becb9c343c8741b4f9d47427a0edb024"
}

I'm trying to get the output as a row for each "id" value:

dfa8eb26632ad060b978212abac639ad
b523b2eb027edadad6fab9d5c265809f
becb9c343c8741b4f9d47427a0edb024

Any help would be much appreciated.

1

Am not a SQL Pro, but this would definitely help you. You are using the wrong function JSON_QUERY will return you again with an array. You need to use JSON_VALUE function to return a value.

Please find the below example which I have tried it in my instance:

Inital Data Extension:

I have added your JSON in one of my column

enter image description here

SQL Query:

Select JSON_VALUE(CustomerID,'$.items[1].id') as CustomerID from MyDE

OR If you want to try with JSON_QUERY as well then use the below SQL :

Select JSON_VALUE(JSON_QUERY(CustomerID,'$.items'),'$[0].id') as CustomerID from MyDE

Result:

b523b2eb027edadad6fab9d5c265809f

I would suggest you to use SSJS using ParseJSON function but i doubt it will support for 5 million rows.

Let me know if this helps.

| improve this answer | |
  • Thanks for that, however the actual JSON data is dynamic (number of id's can be between 1-20). Due to this fact, I can't use indexes to reference each id value. I've managed to change the logic of the code that adds the data to the DE to put the ids in a csv value. Not the best solution, but gets the job done (done using SSJS) – Aditya Gollakota May 27 at 22:20
  • okay That's great! – Naveen VM May 28 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.