How can you select JSON object key values using SQL

Question

I have some records in my database in a column called Swatch, that are JSON to describe a colour swatch. Each record looks something like this:

{"colors":{"color-1":"transparent","color-2":"transparent"}}

Basically it's one big colour object with lots of key value pairs. I would like to manipulate this JSON, using SQL, to achieve something like this instead to allow customisable colour names.

{"colors":[{"name": "Red", "color":"#00000"},{"name": "Green", "color":"#00000"}]

(Hex codes are just for demo purposes, I know they're not legit).

How can I achieve this? It is possible using SQL Server?

Yes you can, but you need to explain how you get from input to output. — Charlieface
– Charlieface, Commented Jul 8, 2022 at 13:33

Giovanni Luisotto · Accepted Answer · 2022-07-08 13:53:46Z

1

It may be possible, but a lot depends on the data and what you are trying to achieve. There is a set of JSON functions available, most of them added in SQL 2016, and a pair more should be available in SQL Server 2022.

If you want to erase the current structure and data in favor of a new one that's easy. If you want to convert your current structure to a different one, that might be pretty hard, but it's highly data related.

Let's consider your samples as the actual data you want to convert

{"colors":{"color-1":"transparent","color-2":"transparent"}}

{"colors":[{"name": "Red", "color":"#00000"},{"name": "Green", "color":"#00000"}]}

Here are the issues and complications I see

how can you say what color-1 is? what name does it have? that's human operation unless you have some ColorCode-ColorName table
Do you have a fixed/known amount of color-N keys? to get the property value cleanly you will need to reference them explicitly. (but I'm sure you can find other ways... without using JSON functions tho)
to change the structure you need to extract the data and build the new JSON string, basically a lot of string concatenation that will require quite some time to build and test

JSON function samples

I've played around with the JSON data and created some sample queries, I hope trying them will help you understand what it means to manipulate JSON data, and choose if SQL Server is a fitting tool for your task

-- key-values property
DECLARE @jdata2 as varchar(200) = '{"colors":{"color-1":"transparent","color-2":"transparent"}}'

SELECT
    JSON_VALUE(@jdata2,'$."colors"."color-1"') AS Color1
    ,JSON_VALUE(@jdata2,'$."colors"."color-2"') AS Color2
    ,JSON_VALUE(@jdata2,'$."colors"."color-3"') AS Color3
GO

-- objects and arrays
DECLARE @jdata as varchar(200) = '{"company":"Contoso","colors":[{"name": "Red", "color":"#00000"},{"name": "Green", "color":"#00000"}]}'

SELECT JSON_QUERY(@jdata,'$.colors[0]')
SELECT JSON_VALUE(@jdata,'$.colors[0].name')
SELECT JSON_VALUE(@jdata,'$.company')

--Update property
SELECT JSON_MODIFY(@jdata,'$.company', 'Northwind')
--Add property
SELECT JSON_MODIFY(@jdata,'$.country', 'Italy')
--Add new Object
SELECT JSON_MODIFY(@jdata,'$.salesman', JSON_QUERY('{"name":"Mario","surname":"Rossi"}'))
--Append new Object in an Object array
SELECT JSON_MODIFY(@jdata,'append $.colors', JSON_QUERY('{"name":"Yellow", "color":"#00000"}','$'))

------ About DELETING
--Delete (whole) Property works fine
SELECT JSON_MODIFY(@jdata,'$.colors', NULL)
-- deleting sometihng inside an array is not fine at all
-- Should delete 1 value/object from the array... but no, 'null,' is left instead
SELECT JSON_MODIFY(@jdata,'$.colors[1]', NULL)
-- To "delete" properly pass the whole array or object array omitting the deleted value...
SELECT JSON_MODIFY(@jdata,'$.colors', JSON_QUERY('[{"name": "Green", "color":"#00000"}]'))

answered Jul 8, 2022 at 13:53

Giovanni Luisotto

1,4008 silver badges12 bronze badges

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

3 Comments

Mr_Happy Over a year ago

Thanks, this is very helpful. Unfortunately looking at my data, I don't always know what the key is. It could be 'color-1', 'Colour 1', 'coleur-1' or 'Color-1'. Is there a way for me to SELECT JSON_VALUE without this? Also, I am unable to use the index because it's not part of an array.

Giovanni Luisotto Over a year ago

You will have to look at string functions... (TSQL is not the best at string transformation). I think STRING_SPLIT() can help you get all the values (by rows)... If the whole process starts getting too complex just solve it outside SQL Server.

Mr_Happy Over a year ago

FYI, I resolved it with lots of string parsing. It wasn't pretty but it works! :)

Collectives™ on Stack Overflow

How can you select JSON object key values using SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related