r/SQL•
planetmatt

Getting a Key Pair list from OPENJSON

SQL Server

With the JSON below, is there a simpler way to produce the key pair list I want without doing 3 x OPENJSON calls and the Subquery?

DECLARE @json NVARCHAR(MAX) = '{ "SearchParameters": [ { "LastName": "Smith" }, { "Org": "AAA" }, { "Postcode": "SW1" } ] }';

SELECT 
(SELECT [key] from OPENJSON([value]) ) AS KeyName
,(SELECT [value] from OPENJSON([value]) ) AS KeyValue
FROM 
(
	SELECT 
	[value]
	FROM OPENJSON(@json,'$.SearchParameters') 
) x

Desired Output

KeyName        KeyValue
LastName	 Smith
Org	         AAA
Postcode	 SW1

Posted Nov 12, 2024, 8:08 PM UTC

213
Loading comments…