多个父表和一个子表的JSON [英] JSON for multiple parent table's and one child table

问题描述

以这样的方式生成JSON:子表的JSON应该具有ARRAY_WRAPPER，父表的JSON不应具有ARRAY_WRAPPER 并且查询应该在('kumar，pathan')中的P1NAME列的PARENT1表上，因此输出应该有2行

Generate JSON in such a way that JSON for child table should have ARRAY_WRAPPER and JSON for parent table should NOT have ARRAY_WRAPPER and Query should be on PARENT1 table on column P1NAME in ('kumar,pathan'), so the output should have 2 rows

我有下面提到的表格

CREATE TABLE [dbo].[Z_PARENT1](
    [P1id] [int] IDENTITY(1,1) NOT NULL,
    [P1NAME] [varchar](50) NULL
)

CREATE TABLE [dbo].[Z_PARENT2](
    [P2id] [int] IDENTITY(1,1) NOT NULL,
    [P1id] [int] NOT NULL,
    [P2NAME] [varchar](50) NULL
)

CREATE TABLE [dbo].[Z_CHILD](
    [Cid] [int] IDENTITY(1,1) NOT NULL,
    [P1id] [int] NOT NULL,
    [CNAME] [varchar](50) NULL
)

INSERT INTO [dbo].[Z_PARENT1] ([P1NAME]) VALUES ('kumar'), ('pathan') , ('chris')
INSERT INTO [dbo].[Z_PARENT2] ([P1id],[P2NAME]) VALUES (1,'Mrs.kumar'), (2,'Mrs.pathan') , (3,'Mrs.chris')
INSERT INTO [dbo].[Z_CHILD] ([P1id],[CNAME]) VALUES (1,'A_kumar'),(1,'B_kumar'),(2,'X_pathan'),(2,'Y_pathan') 

查询应该在('kumar，pathan')中P1NAME列的PARENT1表上，因此查询输出应该有2行，
第1行应如下所示

The query should be on PARENT1 table on column P1NAME in ('kumar,pathan'), so Query Output should have 2 rows,
Row1 should be like below

 {
      "PARENT1":{"P1id":1,"P1NAME":"kumar"},
      "PARENT2":{"P2NAME":"Mrs.kumar"},
      "CHILD":[{"Cid":1,"P1id":1,"CNAME":"A_kumar"},{"Cid":2,"P1id":1,"CNAME":"B_kumar"}]
    }

Row2应该如下所示

Row2 should be like below

 {
      "PARENT1":{"P1id":2,"P1NAME":"pathan"},
      "PARENT2":{"P2NAME":"Mrs.pathan"},
      "CHILD":[{"Cid":3,"P1id":2,"CNAME":"X_pathan"},{"Cid":4,"P1id":2,"CNAME":"Y_pathan"}]
    }

请注意:(重要)

1. 我应该能够在P1NAME IN列('kumar，pathan')条件的PARENT1表上进行查询，因此输出应该有2行
2. 只有孩子应该具有JSON数组. (我的意思是ARRAY_WRAPPER)和parent1& parent2不应该有数组(我的意思是WITHOUT_ARRAY_WRAPPER)
3. 父级和子级之间应保持左连接，因为如果子级没有行，则至少应构造父级JSON.
4. 作为parent1& parent2是主表，在pid上应该有内部联接

推荐答案

如果我正确地关注了您，以下查询将根据您在问题中的要求提供结果.

If I follow you correctly, the following query will produce the result as you request in your question.

SELECT (SELECT TOP 1 IP1.P1id, IP1.P1NAME
        FROM dbo.Z_PARENT1 IP1
        WHERE IP1.P1id = P1.P1id
        FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) AS PARENT1,
        (SELECT TOP 1 IP2.P2NAME
        FROM dbo.Z_PARENT2 IP2
        WHERE IP2.P1id = P1.P1id
        FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) AS PARENT2,
        (SELECT *
        FROM dbo.Z_CHILD IC
        WHERE IC.P1id = P1.P1id
        FOR JSON AUTO) AS CHILD
FROM [dbo].[Z_PARENT1] P1
FOR JSON AUTO

此查询尚未完全优化，但可以用作潜在的解决方案.

This query is not fully optimized, but it can be used as a potential solution.

重要的是要说，因为您有多行它不能是一个对象，所以它应该作为全局结果数组.您可以轻松地扩展此查询并将放在子句中.

It is just important to say, because you have multiple rows it cannot be an object, it should be array as a global result. You can easily expand this query and put where clause.

这篇关于多个父表和一个子表的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！