'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:

Key Values for JSON

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