@david, to answer your questions
Can I query the array inside the JSON using SQL or do I need to get the array separately first?
In order to use 'Query JSON using SQL' you must pass a JSON array into 'JSON text'. So you would have to extract results into its own array with something like 'Retrieve values from JSON'
Can I use a query to search for a matching item in the array. e.g I want to search for id 123 and get the content?
Yes, you can if you specify "Flatten nested JSON" as true this will convert any nested items in your json objects into a flattened JSON, so essentially your JSON would behave like this
[
{
"id": 3054,
"properties.content": "abc"
},
{
"id": 123,
"properties.content": "bcd"
}
]
And to select the value, you can write SQL like this
SELECT properties_content FROM CSV WHERE id = '123'
Alternatively, instead of using 'Query a JSON using SQL' you can achieve a similar result with 'Retrieve values from JSON' with the following configuration:
JSON Text
{
"total": 5,
"results": [
{
"id": 3054,
"properties": {
"content": "abc"
}
},
{
"id": 123,
"properties": {
"content": "bcd"
}
}
]
}
JSON path
$.results[?(@.id == 123)].properties.content
