Azure Data Factory Get Metadata

Question

I am creating a Pipeline and pulling the metadata structure into an array. I am then converting the array into a string.

I get the following.

DECLARE @JSON NVARCHAR(MAX);
SET @JSON = N'

{
    "name": "jsondatastring",
    "value": "[{\"name\":\"sszid\",\"type\":\"String\"},{\"name\":\"requestid\",\"type\":\"String\"},{\"name\":\"changeid_fk\",\"type\":\"Int32\"},{\"name\":\"status\",\"type\":\"String\"},{\"name\":\"boro\",\"type\":\"String\"},{\"name\":\"on_street\",\"type\":\"String\"},{\"name\":\"from_street\",\"type\":\"String\"},{\"name\":\"to_street\",\"type\":\"String\"},{\"name\":\"bump\",\"type\":\"Boolean\"},{\"name\":\"mph\",\"type\":\"Int32\"},{\"name\":\"beacon\",\"type\":\"Boolean\"},{\"name\":\"install_reason\",\"type\":\"String\"},{\"name\":\"submit_date\",\"type\":\"DateTime\"},{\"name\":\"install_date\",\"type\":\"DateTime\"},{\"name\":\"install_note\",\"type\":\"String\"},{\"name\":\"date_created\",\"type\":\"DateTime\"},{\"name\":\"date_updated\",\"type\":\"DateTime\"}]"
}
'

I am unable to read this with SQL OPENJSON.  


SELECT [key] as fieldOrder,
    JSON_VALUE(value, 'strict $.name') AS fieldName,
    JSON_VALUE(value, 'strict $.type') AS fieldType
FROM OPENJSON(@json, '$')

Here is the error I am getting

Msg 13609, Level 16, State 2, Line 12

JSON text is not properly formatted. Unexpected character 'j' is found at position 0.

Answer 1

It is not OPENJSON that is barking at you, but JSON_VALUE. If you change the query to:

SELECT [key] as fieldOrder, value
    --JSON_VALUE(value, 'strict $.name') AS fieldName,
    --JSON_VALUE(value, 'strict $.type') AS fieldType
FROM OPENJSON(@json, '$')

You can see what you actually are passing to JSON_VALUE.

Answer 2

Try this:

; WITH CTE AS (  
  SELECT value
  FROM OPENJSON(@JSON, '$')
  WHERE "key"  = 'value'
)
SELECT [key] as fieldOrder,
    JSON_VALUE(js.value, 'strict $.name') AS fieldName,
    JSON_VALUE(js.value, 'strict $.type') AS fieldType
FROM  CTE
CROSS APPLY OPENJSON(CTE.value, '$') js