How to check whether a specific value exists or not in JSON string in SQL-CodePudding

I am having a JsonString as

[
  {
    "CountryId": "A26BF8CF-11A4-4A30-B47E-1CC0C3AC6D69",
    "CountryName": "Circuit_0",
    "Data": [
      {
        "SystemType": "Central",
        "SystemTypeId": "EF1FE7E8-6FF9-447E-9DAD-69D0EAAE0642",
        "ReferenceGuid": "04618DE5-B92B-41A3-B0EB-1EE8E4C329CC",
        "System": [
          {
            "System": "Bank",
            "SystemId": "3FC19ED5-A08C-4E2B-999C-37D6D83F7D65"
          }
        ]
      }
    ],
    "IsCascade": false
   
  },
  {
    "CountryId": "4645CEE6-5061-4DB8-8E81-C7B1DB417E38",
    "CountryName": "Circuit_1",
    "Data": [
      {
        "SystemType": "State",
        "SystemTypeId": "94E1BEB7-BC8F-4D94-8027-3D53E6C9EB01",
        "ReferenceGuid": "289F5B6D-7CA1-422C-83D3-2688A88D18C5",
        "System": [
          {
            "System": "PF",
            "SystemId": "ADA9EBFA-DDCE-4CFA-9A07-10BCFD1232A3",
            "WinterTemp": "75"
          }
        ],
        "Cooling": "false"
      }
    ],
    "Total": "400"
  }
]

I want to check whether a SystemId= "3FC19ED5-A08C-4E2B-999C-37D6D83F7D65" exists or not. If exists i want to return true or else false

I tried using OPENJSON like below, but data is truncating.

DECLARE @Input NVARCHAR(MAX) = N'[{"CountryId":"A26BF8CF-11A4-4A30-B47E-1CC0C3AC6D69","CountryName":"Circuit_0","Data":[{"SystemType":"Central","SystemTypeId":"EF1FE7E8-6FF9-447E-9DAD-69D0EAAE0642","ReferenceGuid":"04618DE5-B92B-41A3-B0EB-1EE8E4C329CC","System":[{"System":"Bank","SystemId":"3FC19ED5-A08C-4E2B-999C-37D6D83F7D65"}]}],"IsCascade":false},{"CountryId":"4645CEE6-5061-4DB8-8E81-C7B1DB417E38","CountryName":"Circuit_1","Data":[{"SystemType":"State","SystemTypeId":"94E1BEB7-BC8F-4D94-8027-3D53E6C9EB01","ReferenceGuid":"289F5B6D-7CA1-422C-83D3-2688A88D18C5","System":[{"System":"PF","SystemId":"ADA9EBFA-DDCE-4CFA-9A07-10BCFD1232A3","WinterTemp":"75"}],"Cooling":"false"}],"Total":"400"}]';
Select * from OPENJSON(
(Select Data from OPENJSON(@Input)
WITH (Data NVARCHAR(MAX)  AS JSON) 
for json path)) With (System NVARCHAR(MAX)  AS JSON)

Could someone help me how to do this

CodePudding user response：

Because you have nested JSON, you need to extract those values in separate OPENJSON commands, which you can then CROSS APPLY to the original data. This query will give you all the SystemId values:

SELECT c.SystemId 
FROM OPENJSON(@Input)
WITH (Data NVARCHAR(MAX)  AS JSON) a
CROSS APPLY OPENJSON(a.Data)
WITH (System NVARCHAR(MAX)  AS JSON) b
CROSS APPLY OPENJSON(b.System) 
WITH (SystemId NVARCHAR(MAX) '$.SystemId') c

Output for your sample data:

SystemId
3FC19ED5-A08C-4E2B-999C-37D6D83F7D65
ADA9EBFA-DDCE-4CFA-9A07-10BCFD1232A3

Demo on dbfiddle.uk

To get a true/false indication as to whether a value exists, you could do something like this:

SELECT SUM(CASE WHEN c.SystemId = N'3FC19ED5-A08C-4E2B-999C-37D6D83F7D65' THEN 1 ELSE 0 END) AS IdExists
FROM OPENJSON(@Input)
WITH (Data NVARCHAR(MAX)  AS JSON) a
CROSS APPLY OPENJSON(a.Data)
WITH (System NVARCHAR(MAX)  AS JSON) b
CROSS APPLY OPENJSON(b.System) 
WITH (SystemId NVARCHAR(MAX) '$.SystemId') c

Output for your sample data:

IdExists
1

Demo on dbfiddle.uk

If you're getting your data from another query, you can use it as a subquery in either the OPENJSON or the FROM clause. For example:

SELECT c.SystemId 
FROM OPENJSON((SELECT TOP 1 JsonColumnString FROM test))
WITH (Data NVARCHAR(MAX)  AS JSON) a
CROSS APPLY OPENJSON(a.Data)
WITH (System NVARCHAR(MAX)  AS JSON) b
CROSS APPLY OPENJSON(b.System) 
WITH (SystemId NVARCHAR(MAX) '$.SystemId') c

SELECT c.SystemId 
FROM (SELECT TOP 1 JsonColumnString FROM test) t
CROSS APPLY OPENJSON(t.JsonColumnString)
WITH (Data NVARCHAR(MAX)  AS JSON) a
CROSS APPLY OPENJSON(a.Data)
WITH (System NVARCHAR(MAX)  AS JSON) b
CROSS APPLY OPENJSON(b.System) 
WITH (SystemId NVARCHAR(MAX) '$.SystemId') c

Demo on dbfiddle.uk