如何使用SQL选择JSON对象键值

Question

我的数据库中的一些记录在一个名为“ Swatch”的列中，这是描述颜色色板的JSON。每个记录看起来像这样：

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

基本上是一个具有许多钥匙值对的大色对象。我想使用SQL操纵此JSON，以实现类似的东西，以允许可自定义的颜色名称。

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

（十六进制代码仅用于演示，我知道它们不合法）。

我该如何实现？可以使用SQL Server吗？

Answer 1

可能是可能的，但是很大程度上取决于数据以及您要实现的目标。
有一组 JSON功能可用，其中大多数在SQL 2016中添加，并且SQL中应提供更多功能服务器2022。

如果您想删除当前的结构和数据，以支持一个简单的新结构。
如果您想将当前结构转换为另一种结构，那可能很难，但这与数据相关。

让我们将您的样本视为您要转换的实际数据

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

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

是问题和复杂性，我知道

• 如何说color-1是什么？它有什么名字？那是人类操作，除非您有一些颜色代码颜色表，否则
• 您是否有固定/已知数量的color-n键？要清洁属性价值，您需要明确参考它们。 （但是我确定您可以找到其他方法...而无需使用JSON函数）
• 更改您提取数据并构建新的JSON字符串所需的结构构建和测试

JSON函数示例

我已经播放了JSON数据并创建了一些示例查询，我希望尝试它们会帮助您了解操纵JSON数据的含义，并选择SQL Server是否为适合您的合适工具 任务

-- 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"}]'))