'OpenJson - Need to Flatten & Write Key Values & Objects to Columns
Given the following JSON structure, I'm struggling to find correct format to write the keys and values for 'users" to separate relational columns:
Desired output would be to explode the values for the key "users" in image above into separate columns (i.e. id, full_name, address1, address2, city, state, zip, account_id).
I've tried adding WITH clause but only 'NULL' is returned:
SELECT * FROM OPENJSON(@json,N'$.users') WITH ( [id] int '$.id', [fullname] varchar(100) '$.full_name');
Full json structure below:
DECLARE @json NVARCHAR(MAX)
SET @json=N'{
"count": 2,
"results": [
{
"key": "users",
"id": "1234"
},
{
"key": "users",
"id": "2345"
}
],
"users": {
"1234": {
"full_name": "Jane Doe",
"email_address": "[email protected]",
"address": [
"address1",
"address2",
"city",
"state",
"zip"
],
"account_id": "00001",
"id": "1234"
},
"2345": {
"full_name": "John Smith",
"email_address": "john.smith@emailco",
"address": [
"address1",
"address2",
"city",
"state",
"zip"
],
"account_id": "00002",
"id": "2345"
}
},
"meta": {
"count": 2,
"page_count": 1,
"page_number": 1,
"page_size": 20
}
}';
SELECT *
FROM OPENJSON(@json)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|