Create JSON from SQL Server Table With Column Value as JSON Property Name

Question

I have a table with the following schema in a SQL Azure DB (2019 compat level):

CREATE TABLE dbo.Properties
(
    PropertyId int, 
    PropertyName nvarchar(100),
    PropertyValue nvarchar(1000)
)

I'd like to take the data within this table and turn it into JSON using the value within the PropertyName column as the name of the JSON property, and obviously the PropertyValue value as the JSON property value.

EDIT 12/10/2021: Importantly, the values within the PropertyName column will not be predictable ahead of time.

For example, consider this data in the table (3 rows):

1, "Color", "Blue"
1, "Name", "John"
1, "Cost", 5

The above would be turned into the following JSON:

{"Color":"Blue", "Name":"John", "Cost":5}

I'm obviously able to do this with a STRING_AGG function like the following:

SELECT '{' + STRING_AGG( '"' + p.PropertyName + '": ''' + p.PropertyValue,''',')  
WITHIN GROUP (ORDER BY p.PropertyName) + '}' AS MyJson
FROM dbo.Properties p
GROUP BY p.Id

But I was hoping to use one of the build in JSON functions rather than hack together a big string.

I think this can help you : https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15 — Maxime Challon
– Maxime Challon, Commented Dec 4, 2021 at 17:07
This is really the only way to do it dynamically. But you should use STRING_ESCAPE(), because your names and values may contain invalid JSON — Charlieface
– Charlieface, Commented Dec 4, 2021 at 18:40
Did you ever figure out a dynamic solution to this issue? I'm running up against the same thing now. I'd like an elegant query for this. — Mark Rullo
– Mark Rullo, Commented Apr 20, 2022 at 19:27

wBob · Accepted Answer · 2021-12-04 17:20:18Z

3

FOR JSON AUTO works from the column names, so one method to get your desired result would be to PIVOT the property names into columns. For example:

SELECT Color, [Name], Cost 
FROM dbo.Properties
PIVOT ( MAX( PropertyValue ) For PropertyName In ( [Color], [Name], Cost ) ) pvt
FOR JSON AUTO;

My results:

Of course this is only convenient if your JSON attributes / column names are always known and it's a simple example. For more complex examples, you are probably looking at dynamic pivot, or dynamic SQL and your STRING_AGG example isn't so bad.

answered Dec 4, 2021 at 17:20

wBob

14.4k3 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RMD Over a year ago

I neglected to say in my original question that the property names aren't predictable. In other words, I won't know it's Color, Name, and Cost.

wBob Over a year ago

Fair enough. You’d just be looking at a dynamic pivot then.

Stefan Steiger Over a year ago

Also, that returns an array instead of an object.

wBob Over a year ago

Feel free to add the WITHOUT_ARRAY_WRAPPER for your particular use case!

Collectives™ on Stack Overflow

Create JSON from SQL Server Table With Column Value as JSON Property Name

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related