CODEWITHSUNDEEP
sqljsonsql-server
Pan Markosian
Pan Markosian

Reputation: 101

SQL JSON_VALUE select parent values

I have this JSON and I need to extract 2 strings

  1. Childs of "properties" in a column separated by commas.
  2. Childs of "title" in a column separated by commas.

The results should be

  1. BoxTp, boxNo

  2. Box Type, Box Number

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "properties": {
        "BoxTp": {
            "title": "Box Type",
            "type": [
                "null",
                "string"
            ],
            "description": "Type"
        },
        "boxNo": {
            "title": "Box Number",
            "type": [
                "integer",
                "null"
            ],
            "description": "Box No.",
            "format": "int32"
        }
    }
}

Upvotes: 2

Views: 393

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You simply need to parse the input JSON and concatenate the results. Of course, you need SQL Server 2016+ to use the built-in JSON support and SQL Server 2017+ for string aggregation with STRING_AGG():

DECLARE @json nvarchar(max) = N'{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "properties": {
        "BoxTp": {
            "title": "Box Type",
            "type": [
                "null",
                "string"
            ],
            "description": "Type"
        },
        "boxNo": {
            "title": "Box Number",
            "type": [
                "integer",
                "null"
            ],
            "description": "Box No.",
            "format": "int32"
        }
    }
}'

SELECT STRING_AGG([key], ', ') AS ChildsOfProperties
FROM OPENJSON(@json, '$.properties')

SELECT STRING_AGG(j2.title, ', ') AS ChildsOfTitles
FROM OPENJSON(@json, '$.properties') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (title nvarchar(100) '$.title') j2

Result:

ChildsOfProperties
-----------------
BoxTp, boxNo

ChildsOfTitles
--------------------
Box Type, Box Number

If you need a single statement:

SELECT 
   STRING_AGG(j1.[key], ', ') AS ChildSOfProperties,
   STRING_AGG(j2.title, ', ') AS ChildSOfTitles
FROM OPENJSON(@json, '$.properties') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (title nvarchar(100) '$.title') j2

Upvotes: 2

Related Questions