wake-up-neo.com

Erstellen Sie verschachtelte JSON-Arrays mit FOR JSON PATH

Ich muss eine JSON-Ausgabe aus einer Abfrage erstellen, die den inneren Join zwischen zwei Tabellen mit einer Eins-zu-Viele-Beziehung verwendet.
Ich möchte, dass die Werte der sekundären Tabelle als Array-Eigenschaften der primären Tabelle verschachtelt werden.

Betrachten Sie das folgende Beispiel:

DECLARE @Persons AS TABLE
(
    person_id int primary key,
    person_name varchar(20)
)

DECLARE @Pets AS TABLE
(
    pet_owner int, -- in real tables, this would be a foreign key
    pet_id int  primary key,
    pet_name varchar(10)
)

INSERT INTO @Persons (person_id, person_name) VALUES
(2, 'Jack'),
(3, 'Jill')

INSERT INTO @Pets (pet_owner, pet_id, pet_name) VALUES
(2, 4, 'Bug'),
(2, 5, 'Feature'),
(3, 6, 'Fiend')

Und fragen Sie:

DECLARE @Result as varchar(max)
SET @Result =
(
SELECT  person_id as [person.id],
        person_name as [person.name],
        pet_id as [person.pet.id],
        pet_name as [person.pet.name]
FROM @Persons 
JOIN @Pets ON person_id = pet_owner
FOR JSON PATH, ROOT('pet owners')
)

PRINT @Result

Dadurch wird der folgende JSON gedruckt:

{
    "pet owners":
    [
    {"person":{"id":2,"name":"Jack","pet":{"id":4,"name":"Bug"}}},
    {"person":{"id":2,"name":"Jack","pet":{"id":5,"name":"Feature"}}},
    {"person":{"id":3,"name":"Jill","pet":{"id":6,"name":"Fiend"}}}
    ]
}

Ich möchte jedoch die Haustierdaten als Arrays in den Eigentümerdaten haben:

{
    "pet owners":
    [
        {
            "person":
            {
                "id":2,"name":"Jack","pet":
                [
                    {"id":4,"name":"Bug"},
                    {"id":5,"name":"Feature"}
                ]
            }
        },
        {
            "person":
            {
                "id":3,"name":"Jill","pet":
                {"id":6,"name":"Fiend"}
            }
        }
    ]
}

Wie kann ich das machen?

17
ATC

Sie können die folgende Abfrage verwenden:

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
    (
        SELECT pt.pet_id AS id, pt.pet_name AS name 
        FROM @Pets pt WHERE pt.pet_owner=pr.person_id 
        FOR JSON PATH
    ) AS [person.pet]
FROM @Persons pr 
FOR JSON PATH, ROOT('pet owners')

Weitere Informationen finden Sie unter https://blogs.msdn.Microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

23
Razvan Socol

Mit tief verschachtelten Arrays können die Unterabfragen schnell nicht mehr verwaltet werden:

select id,foo, (select id, bar, (select ... for json path) things, 
(select...) more_things) yet_more, select(...) blarg

Ich erstelle eine relationale (Nicht-JSON-) Ansicht, die alle meine Tabellen verknüpft und deren JSON-Struktur in die Spaltenaliasnamen eingebettet ist, genau wie für JSON-Pfad. Ich muss aber auch [] angeben, dass der JSON-Knoten ein Array ist. So was:

select p.id [id], p.foo [foo], c.name [children[].name], c.id [children[].id],
gp.name [grandparent.name], gc.name [children[].grandchildren[].name]
from parent p
join children c on c.parent_id = p.id .....

Ich habe eine gespeicherte Prozedur geschrieben, die eine JSON-Ansicht in die Nicht-JSON-Ansicht erstellt, die die Spaltennamen der relationalen Ansicht analysiert und den JSON hübsch macht. Siehe unten. Nennen Sie es mit dem Namen Ihrer relationalen Ansicht und es wird eine Ansicht erstellt. Es ist nicht gründlich getestet, aber es funktioniert für mich. Die einzige Einschränkung ist, dass Tabellen id Spalten mit dem Namen id. haben müssen. Es werden string_agg () und json_array () verwendet Die Version von SQL muss ziemlich neu sein. Es ist auch so eingerichtet, dass ein Array im Stammverzeichnis zurückgegeben wird. Es muss angepasst werden, um ein Objekt zurückzugeben.


create procedure create_json_from_view
@view_name varchar(max)
as

create table #doc_schema (
    node_level int,             -- nesting level starting with 0
    node_name varchar(max),     -- alias used for this nodes query
    node_path varchar(max),     -- full path to this node
    parent_path varchar(max),   -- full path to it's parents 
    is_array bit,               -- is node marked as array by ending with []
    select_columns varchar(max),-- comma separated path/alias pairs for selected columns on node
    group_by_columns varchar(max), -- comma separated paths for selected columns on node. group by is necessary to prevent duplicates
    node_parent_id varchar(max),   -- the id column path to join subquery to parent. NOTE: ID COLUMN MUST BE CALLED ID
    from_clause varchar(max),   -- from clause built from above fields
    node_query varchar(max)     -- complete query built from above fields
)

/* get each node path from view schema
*/
INSERT INTO #doc_schema (node_path)
select distinct LEFT(COLUMN_NAME,CHARINDEX('.'+ VALUE + '.',COLUMN_NAME) + LEN(VALUE)) node_path 
FROM INFORMATION_SCHEMA.COLUMNS 
CROSS APPLY STRING_SPLIT(COLUMN_NAME, '.') 
WHERE CHARINDEX('.',COLUMN_NAME) > 0
AND RIGHT(COLUMN_NAME,LEN(VALUE)) <> VALUE
and table_name = @view_name

/* node_name past rightmost period or the same as node_path if there is no period
also remove [] from arrays
*/
update #doc_schema set node_name = 
case when charindex('.',node_path) = 0 then replace(node_path,'[]','')
else REPLACE(right(node_path,charindex('.',reverse(node_path)) - 1),'[]','') end

/* if path ends with [] node is array
    escapes are necessary because [] have meaning for like
*/
update #doc_schema set is_array =
case when node_path like '%\[\]' escape '\' then 1 else 0 end --\

/* parent path is everything before last . in node path
    except when the parent is the root, in which case parent is empty string
*/
update #doc_schema set parent_path = 
case when charindex('.',node_path) = 0 then ''
else left(node_path,len(node_path) - charindex('.',reverse(node_path))) end

/* level is how many . in path. an ugly way to count.
*/
update #doc_schema set node_level = len(node_path) - len(replace(node_path,'.','')) + 1

/* set up root node
*/
insert into #doc_schema (node_path,node_name,parent_path,node_level,is_array)
select '','',null,0,1

/* I'm sorry this is so ugly. I just gave up on explaining
    all paths need to be wrapped in [] and internal ] need to be escaped as ]]
*/
update #doc_schema set select_columns = sub2.select_columns, group_by_columns = sub2.group_by_columns
from (
    select node_path,string_agg(column_path + ' ' + column_name,',') select_columns,
    string_agg(column_path,',') group_by_columns
    from (
        select ds.node_path,'['+replace(c.COLUMN_NAME,']',']]')+']' column_path,replace(c.column_name,ds.node_path + '.','') column_name
        from INFORMATION_SCHEMA.COLUMNS c
        join #doc_schema ds
        on (charindex(ds.node_path + '.', c.COLUMN_NAME) = 1
        and charindex('.',replace(c.COLUMN_NAME,ds.node_path + '.','')) = 0)
        or (ds.node_level = 0 and charindex('.',c.COLUMN_NAME) =  0)
        where table_name = @view_name
    ) sub
    group by node_path
) sub2
where #doc_schema.node_path = sub2.node_path

/* id paths for joining subqueries to parents
    Again, the need to be wrapped in [] and and internal ] need to be escaped as ]]
*/
update #doc_schema set node_parent_id  = 
    case when parent_path = '' then '[id]' 
    else '[' + replace(parent_path,']',']]')+'.id]'
    end

/* table aliases for joining subqueries to parents need to be unique
    just use L0 L1 etc based on nesting level
*/
update #doc_schema set from_clause =
    case when node_level = 0 then ' from ' + @view_name + ' L'+cast(node_level as varchar(4)) + ' '
    else ' from ' + @view_name + ' L'+cast(node_level as varchar(4))+' where L'+cast(node_level - 1 as varchar(4))+'.'+ node_parent_id + 
        '  = L'+cast(node_level as varchar(4))+'.'+ node_parent_id 
    end

/* Assemble node query from all parts
    ###subqueries### is a place to put subqueries for node
*/
update #doc_schema set  node_query = 
        ' (select ' + select_columns + ', ###subqueries###' + from_clause 
        + ' group by '+ group_by_columns
        +' for json path) '

/* json path will treat all objects as arrays so select first explicitly
    to prevent [] in json
*/  
update #doc_schema set  node_query =    
    case when is_array = 0
    then '(select JSON_query(' + node_query + ',''$[0]'')) ' + node_name
    else node_query +  + node_name end

/* starting with highest nesting level substitute child subqueries ino
    subquery hold in their parents
*/
declare @counter int = (select max(node_level) from #doc_schema)

while(@counter >= 0)
begin
    update #doc_schema set node_query = replace(node_query,'###subqueries###', subs.subqueries)
    from
    (select parent_path, string_agg(node_query,',') subqueries, node_level from #doc_schema
    group by parent_path, node_level ) subs
    where subs.node_level = @counter and 
    #doc_schema.node_path = subs.parent_path

    set @counter -= 1
end

/* objects and arrays with no subobjects or subarrays still have subquery holder so remove them
*/
update #doc_schema set node_query = replace(node_query,', ###subqueries###', '') where node_level = 0

declare @query nvarchar(max) = (select node_query from #doc_schema where node_level = 0)

/* add wrapper to query to specify column nave otherwise create view will fail
*/
set @query = 
    case when OBJECT_ID(@view_name + '_JSON', 'V') is NULL then 'create' else 'alter' end +
    ' view ' + @view_name + '_json as select' + @query + ' json'

exec sp_executesql @query
0
user3170574