Read JSON in ADF

Question

In Azure Data Factory, I need to be able to process a JSON response. I don't want to hardcode the array position in case they change, so something like this is out of the question:

@activity('Place Details').output.result.components[2].name

How can I get the name 123 where types = number given a JSON array like below:

"result": {
    "components": [
        {
            "name": "ABC",
            "types": [
                "alphabet"
            ]
        },
        {
            "name": "123",
            "types": [
                "number"
            ]
        }
    ]
}

Answer 1

One example using the OPENJSON method:

DECLARE @json NVARCHAR(MAX) = '{
    "result": {
        "components": [
            {
                "name": "ABC",
                "types": [
                    "alphabet"
                ]
            },
            {
                "name": "123",
                "types": [
                    "number"
                ]
            }
        ]
    }
}'

;WITH cte AS (
SELECT
    JSON_VALUE( o.[value], '$.name' ) [name],
    JSON_VALUE( o.[value], '$.types[0]' ) [types]
FROM OPENJSON( @json, '$.result.components' ) o
)
SELECT [name]
FROM cte 
WHERE types = 'number'

I will have a look at other methods.