CODEWITHSUNDEEP
jsonsql-serverstored-procedures
Roshan
Roshan

Reputation: 3346

Get Children nodes in json string using sql stored procedure?

I have a JSON string passed into stored procedure as follows,

{"config": {
    "site": "Internal",
    "library": "test-library",
    "folderHierarchy": {
        "name": "folder1",
        "children": {
            "name": "folder2",
            "children": {
                "name": "folder3"
            }
        }
    },
    "meta_data": [{
            "meta_text": "date-created",
            "meta_value": "2020-04-17"
        }, {
            "meta_text": "date-modified",
            "meta_value": "2020-04-17"
        }]
}}

I need to get folder names inside folderHierarchy node , it can have many number or folders inside as children.

First I tried to get folderHierarchy as a JSON string and iterate through that JSON string.

DECLARE @CONFIG VARCHAR(MAX) = '{"config": {
    "site": "Internal",
    "library": "test-library",
    "folderHierarchy": {
        "name": "folder1",
        "children": {
            "name": "folder2",
            "children": {
                "name": "folder3"
            }
        }
    },
    "meta_data": [{
            "meta_text": "date-created",
            "meta_value": "2020-04-17"
        }, {
            "meta_text": "date-modified",
            "meta_value": "2020-04-17"
        }]
}}'

DECLARE @folderHierarchy VARCHAR(MAX);
SET @folderHierarchy = JSON_QUERY(@CONFIG, '$.config.folderHierarchy');
SELECT  @folderHierarchy;

SELECT  *
FROM    openjson(@folderHierarchy)
        WITH(
            name VARCHAR(max) '$.name',
            childern NVARCHAR(MAX) '$.children' AS JSON
        ) AS P
        OUTER APPLY OPENJSON(P.childern)
        WITH (name NVARCHAR(MAX) '$.name');

But this give me the first and second folder names.

Can anyone help me to get all the folder names, the main thing is I haven't control about number of child item in a folder.

I mean folder1 can have folder called folder2, and folder 2 can have folder called folder 3, and folder3 can have folder called folder4 etc.. etc..

Upvotes: 0

Views: 1673

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

You could use a Recursive CTE for this. The default settings on SQL Server will allow you to recurse up to 100 levels deep (see MAXRECUSRION)...

declare @Config nvarchar(max) =
N'{"config": {
    "site": "Internal",
    "library": "test-library",
    "folderHierarchy": {
        "name": "folder1",
        "children": {
            "name": "folder2",
            "children": {
                "name": "folder3"
            }
        }
    },
    "meta_data": [{
            "meta_text": "date-created",
            "meta_value": "2020-04-17"
        }, {
            "meta_text": "date-modified",
            "meta_value": "2020-04-17"
        }]
}}';

with JsonFolders as (
    select
        1 as level,
        name,
        children
    from openjson(@Config, N'$.config.folderHierarchy') with (
      name nvarchar(max) N'$.name',
      children nvarchar(max) N'$.children' as json
    )
    union all
    select
        1 + level,
        Child.name,
        Child.children
    from JsonFolders JF
    cross apply openjson(JF.children) with (
      name nvarchar(max) N'$.name',
      children nvarchar(max) N'$.children' as json
    ) Child
)
select * from jsonFolders;

Upvotes: 1

Related Questions