The sql-server OPENJSON() function can take a json array and convert it into sql table with key-value pairs, e.g.:
DECLARE @json NVARCHAR(MAX);
SET @json = '{
"key1": "val1",
"key2": "val2",
"key3": "val3"
}';
SELECT * FROM OPENJSON(@json, '$')
Result:
key value type
--------------------
key1 val1 1
key2 val2 1
key3 val3 1
What is the best general-purpose method for converting this key/value table back into a json array?
Why? If we can do this with a single function, it opens up a range of json modifications which are otherwise not possible on sql server, e.g.:
- Re-order elements
- Rename properties (key names)
- Split json array into smaller arrays / combine json arrays
- Compare json arrays (which key/value elements exists in both jsons? What are the differences?)
- Clean json (remove syntactical whitespace/newlines to compress it)
Now, I could start to do simple CONCAT('"',[key],'":"',[value]), then do a comma-list-aggregration. But if I want a code that is both easy to apply across my codebase and works for all data types, this is not a simple task. By looking at the json format definition, the conversion should take into account a) the 6 different data types, b) escape characters, c) SQL NULL/json null handling, d) what I may have overlooked I.e. at minimum, the below example should be supported:
DECLARE @test_json NVARCHAR(MAX);
SET @test_json = '{
"myNull": null,
"myString": "start_\\_\"_\/_\b_\f_\n_\r_\t_\u2600_stop",
"myNumber": 3.14,
"myBool": true,
"myArray": ["1", 2],
"myObject": {"key":"val"}
}'
SELECT * FROM OPENJSON(@test_json, '$')
Result:
key value type
------------------------------------------------
myNull NULL 0
myString start_\_"_/___ _ _ _☀_stop 1
myNumber 3.14 2
myBool true 3
myArray ["1", 2] 4
myObject {"key":"val"} 5
For the string-aggregation part, we have long suffered the 'FOR XML PATH'-pain. Luckily we have STRING_AGG() on SQL2017/AzureDB, and I will accept a solution depending on STRING_AGG().
