从JSON数组中删除整个表的元素 [英] Remove element from JSON array for whole table

问题描述

我有一列填充了JSON数组.看起来像:

I have a column filled with JSON arrays. It looks like:

Numbers
'[1,33,5,4,5]'
'[1,2,555,4,5]'
'[1,5,3,4,5]'
'[1,25,3,4,5]'
'[1,2,5,4,5]'
'[1,2,3,4,55]'

我要删除所有5个实例(而不是55、555或25)，因此看起来像

I want to remove all instances of 5 (not 55, 555 or 25) so it looks like

Numbers
'[1,33,4]'
'[1,2,555,4]'
'[1,5,3,4]'
'[1,25,3,4]'
'[1,2,4]'
'[1,2,3,4,55]'

我有全文索引，因此我可以使用来识别包含它的行 CONTAINS(Numbers，'5')

I have Full Text Indexing so I can identtify the rows that contain it using CONTAINS(Numbers, '5')

有人知道快速/干净的方法来删除所有5个吗?

Does anyone know a quick/clean way to remove all the 5's?

我知道我可以使用多次替换来替换，5]"，"[5，"和，5"，但似乎是一个麻烦的解决方案.

I know I could use multiple replace to replace ",5]" "[5," and ",5," but seems like a messy solution.

推荐答案

一种无需多个replace的方法是在公用表表达式中使用openjson和string_agg来获取所需的值，然后更新联接到CTE的表.请注意，2017版或更高版本支持string_agg.

One way to do it without multiple replace is to use openjson and string_agg in a common table expression to get the values needed, and then update the table joined to the cte. Please note that string_agg is supported on 2017 version or higher.

首先，创建并填充示例表(请为您在以后的问题中保存此步骤)

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Numbers varchar(50)
)

INSERT INTO @T(Numbers) VALUES
('[1,33,5,4,5]'),
('[1,2,555,4,5]'),
('[1,5,3,4,5]'),
('[1,25,3,4,5]'),
('[1,2,5,4,5]'),
('[1,2,3,4,55]');    

CTE:

WITH CTE AS
(
    SELECT Numbers, '[' + string_agg([Value], ',') +']' As NewNumbers
    FROM @T
    CROSS APPLY
    (SELECT [Value] FROM OPENJSON(Numbers)) As x
    WHERE [Value] != 5
    GROUP BY Numbers
)

更新:

UPDATE T
SET Numbers = NewNumbers
FROM @T As T
JOIN CTE ON T.Numbers = CTE.Numbers

验证:

SELECT *
FROM @T

结果:

Numbers
[1,33,4]
[1,2,555,4]
[1,3,4]
[1,25,3,4]
[1,2,4]
[1,2,3,4,55]

您可以在 DB 上看到一个演示.

You can see a demo on DB<>Fiddle.

但是，replace选项要短得多，并且可以与任何版本的SQL Server一起使用-甚至早于2000年(我认为):

However, the replace option is much shorter and will work with any version of SQL Server - even as old as 2000 (I think):

UPDATE @T 
SET Numbers = 
    REPLACE(
        REPLACE(
            REPLACE(Numbers, '[5,', '[')
        , ',5]', ']')
    , ',5,', '');

总而言之，如果您使用的是2017年或更高版本，并且需要从数组中删除多个值，则cte + string_agg方法可能会更容易(因为您所要做的只是在cte).
对于较旧的版本或单值删除，替换方法可能是更好的选择.

In conclusion, if you are working on 2017 or higher, and need to remove multiple values from an array, the cte + string_agg apporach will probably be easier (since all you have to do is change the where clause in the cte).
For older versions, or for a single value removal, The replace approach might be a better choice.

这篇关于从JSON数组中删除整个表的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！