主页 / dba / 问题 / 241339
Accepted
JOSEFtw
Asked: 2019-06-26 00:20:42 +0800 CST

返回json数组中包含特定值的所有行

  • 772

我有下表：

CREATE TABLE [dbo].[Journals](
    [Id] [nvarchar](450) NOT NULL,
    [CorrelationId] [nvarchar](450) NOT NULL,
    [Version] [int] NOT NULL,
    [Type] [nvarchar](max) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Journals] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Updated]
GO

一行可能如下所示：

Id: 1833eac9-3ea7-4613-be26-af3f7589d6f0
CorrelationId: 0HLNKM700ILBN:00000001
Version: 1
Type: Notification
Data: [{"type": "Event_Received","timeStamp": "2019-06-24T12:47:54.3960942Z","data": {"version": 1,"notificationName": "My Notification","receiverName": "John Doe","receiverUserIdentifier": "123456789","receiverEmail": "[email protected]","senderName": "Some Sender","senderEmail": "[email protected]","contentSubject": "This is my subject","contentBody": "And this is my body","attachmentNames": []},"sensitive": true}, {"type": "Event_ValidationSucceded","timeStamp": "2019-06-24T12:47:54.4167034Z","data": "{}","sensitive": false}, {"type": "Event_PublishStart","timeStamp": "2019-06-24T12:47:54.4691455Z","data": "{}","sensitive": false}, {"type": "Event_PublishSucceeded","timeStamp": "2019-06-24T12:47:54.4740542Z","data": "{}","sensitive": true}]
Created: 2019-06-19 12:29:20.8100000
Updated: 2019-06-19 12:30:51.1123311

我想从包含 Sensitive = true 的数组条目的行中获取所有 Id

所以我尝试了这样的事情：

SELECT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1

这可行，但我得到了重复的 id（因为数组包含多个 Sensitive = true 的项目）。

如何限制查询，以便当数组包含多个 Sensitive = true 的项目时，我不会收到重复的 Id？

想要的输出：

1833eac9-3ea7-4613-be26-af3f7589d6f0

实际输出：

1833eac9-3ea7-4613-be26-af3f7589d6f0
1833eac9-3ea7-4613-be26-af3f7589d6f0

我想我可以在 SELECT DISTINCT Id 中添加一个 DISTINCT 但这感觉像是在作弊，或者？:D

sql-server json

1 个回答

  1. Best Answer

    您可以选择TOP(1)在CROSS APPLY

    SELECT Id
FROM Journals journals
CROSS APPLY 
(
SELECT TOP(1) Sensitive
FROM OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive')
WHERE Sensitive = 1
) as jsonValues;

    或者，正如你提到的DISTINCT

    SELECT DISTINCT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1;

    两者的主要区别

    使用这些TOP(1)值时，将过滤这些值，并且将在执行计划的早期应用顶部运算符

    在此处输入图像描述

    使用该DISTINCT方案时，过滤和分组仅在JSON函数被（交叉）应用后进行

    在此处输入图像描述

    这就是为什么我会选择TOP(1)+CROSS APPLY解决方案。

    作为旁注，存储唯一标识符nvarchar()并不理想，尤其是在使用像nvarchar(450). 考虑在列数据类型中使用最少或接近最少的空间并使用uniqueidentifier数据类型。

    • 2

相关问题