Working With JSON Arrays in SQL

Question

I'm new to JSON and had this dropped on me by the developers saying the systems I use SSRS/Query from will now contain JSON Arrays.

I have picked it up a little via MSDN articles/a few on here but I would like to know if there's a more efficient or easier way to achieve what I'm trying.

All I want to do is count how many times "Non-Compliance" occurs inside an array (it could be 1 or more times depending on the form submitted, so never a fixed amount)

Here is what I am working with;

  DECLARE @json NVARCHAR(4000) = N'{


"FormId": "3eb068fe-77c3-4f95-99fc-8313c00ce768",
  "FormName": "Test Form",
  "FormVersion": 1.0,
  "Sections": [
    {
      "Id": "36e612c9-9113-48a7-9415-c9b7200e7376",
      "Name": "General Details Section",
      "Title": "General",
      "Fields": [
        {
          "Id": "a4cedad6-483b-4b42-b42e-12f048b5474e",
          "Label": "Was the Job Done Safely?",
          "ValueDataType": "System.String",
          "Value": "No",
          "SubFields": [
            {
              "Id": "36593287-bbc4-42cd-914a-eef93a85c6d7",
              "Label": "What has been done to resolve this?",
              "ValueDataType": "System.String",
              "Value": "TEST"
            },
            {
              "Id": "49164866-3afe-4842-aa6f-85312fd3d558",
              "Label": "When was this Resolved?",
              "ValueDataType": "System.DateTime",
              "Value": "2018-05-04T00:00:00+01:00"
            }
          ]
        }
      ]
    },
    {
      "Id": "7b2e4eb9-6f2c-422b-a9fd-c813df293aa5",
      "Name": "Works Review",
      "Title": "Works Review",
      "Fields": [
        {
          "Id": "04a0c54b-7de5-4a14-8ee5-75dade12bfe4",
          "Label": "Is the Reinstatement correct?",
          "ValueDataType": "System.String",
          "Value": "Non Compliant",
          "SubFields": [
            {
              "Id": "36593287-bbc4-42cd-914a-eef93a85c6d7",
              "Label": "What has been done to resolve this?",
              "ValueDataType": "System.String",
              "Value": "TEST"
            },
            {
              "Id": "49164866-3afe-4842-aa6f-85312fd3d558",
              "Label": "When was this Resolved?",
              "ValueDataType": "System.DateTime",
              "Value": "2018-05-04T00:00:00+01:00"
            }
          ]
        },
        {
          "Id": "93b4e405-921c-48b3-9dc4-8a1363fb09c9",
          "Label": "Is the SLG correct on site?",
          "ValueDataType": "System.String",
          "Value": "Non Compliant",
          "SubFields": [
            {
              "Id": "a7847ef3-c413-4a3e-8b6c-2fef4085f77c",
              "Label": "What has been done to resolve this?",
              "ValueDataType": "System.String",
              "Value": "TEST"
            },
            {
              "Id": "18af548a-3ac5-46e6-ac27-a68232d5670a",
              "Label": "When was this Resolved?",
              "ValueDataType": "System.DateTime",
              "Value": "2018-05-04T00:00:00+01:00"
            },
            {
              "Id": "9107d373-4207-4e58-9a85-22e466a2c4c7",
              "Label": "How many Barriers are on site?",
              "ValueDataType": "System.Decimal",
              "Value": 4.0
            }
          ]
        }
      ]
    }
  ]
}';

    SELECT *
FROM OPENJSON(@json,'$.Sections[1].Fields[0]') a
CROSS APPLY OPENJSON(@json, '$.Sections') b

As you can see I'm learning how to step into the data but is there a better way of checking several "Sections" and "Fields" at once to get the data I am looking for?

Thanks for your time.

Benzz · Accepted Answer · 2018-05-18 09:33:20Z

2

I answered my own question I think;

SELECT COUNT(*)
FROM
        (
        SELECT [a].[value]
        FROM OPENJSON(@json,'$.Sections[1].Fields[0]') [a]
        CROSS APPLY OPENJSON(@json, '$.Sections') [b]
        ) [a]
WHERE [value] = 'Non Compliant'

If there are any better ways, please feel free to let me know and I will update the answer (updated the code a little in the Question too)

answered May 18, 2018 at 9:33

Benzz

1191 gold badge3 silver badges15 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

This works, because there is one Non compliant in the second section ($.Section[1]) in the first Field (.Fields[0]). This is coincidence. Your CROSS JOIN will return two rows, because there are two sections. This result seems to be correct but is not what you expect.

Gottfried Lesigang · Accepted Answer · 2018-05-22 11:15:17Z

1

I'm afraid your own answer is coincidence... Try this:

    SELECT [a].*
          ,[b].value
          ,JSON_VALUE([b].value,N'$.Value') AS TheValueWithinField
    FROM OPENJSON(@json,N'$.Sections') [a]
    OUTER APPLY OPENJSON(a.value, N'$.Fields') [b]

You can dive into Sections then use the returned object in a.value to dive deeper into Fields. Finally use JSON_VALUE to get the Value. Against this you can use a simple WHERE

edited May 22, 2018 at 11:15

answered May 18, 2018 at 11:19

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

Benzz Over a year ago

This is great for OPENJSON, my JSON items are actually stored inside a SQL field as NVARCHAR. How would I query against this as when I set a variable this has to be "TOP 1" which the data set has many rows for evaluation. e.g. [Data] (this contains the original question data per row) I think I need to use JSON_VALUE but then this does not seem to help me multi select how many times Non-Compliant appears per row inside the JSON field.

Collectives™ on Stack Overflow

Working With JSON Arrays in SQL

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related