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 Answer 1

3

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.

2
  • 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) May 27, 2020 at 22:20
  • okay That's great!
    – Naveen VM
    May 28, 2020 at 5:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .