SQL查询，以逗号分隔json数组值

zfciruhq 于 2023-05-30 发布在其他

关注(0)|答案(1)|浏览(214)

下面是Json对象。我需要用逗号（，）分隔任务名称。

{
  "Model": [
    {
      "ModelName": "Test Model",    
      "Object": [
         {
           "ID": 1,
           "Name": "ABC",
           "Task" : [
             {
                TaskID : 1222,
                Name: "TaskA"
             },
             {
                TaskID : 154,
                Name: "TaskB"
             }
           ]
         },
         {
           "ID": 11,
           "Name": "ABCD",
           "Task" : [
             {
                TaskID : 222,
                Name: "TaskX"
             },
             {
               TaskID : 234,
               Name: "TaskY"
             }
           ]
        },         
     ]  
 }]}

预期输出应在下表中。我需要的任务名称应逗号分隔。

ModelName   ObjectID   ObjectName  TaskName
Test Model     1          ABC      TaskA, TaskB
Test Model     11         ABCD     TaskX, TaskY

我尝试了下面的查询。但我不知道如何分组任务名称。

SELECT   S1.ModelName,
                           S2.ID  AS ObjectID, 
                           S2.Name AS ObjectName, 
                           S3.TaskName
                     FROM TableA 
                       CROSS APPLY OPENJSON(JsonData)
                         WITH (Model NVARCHAR(MAX) '$.Model[0]' AS JSON) S1
                       CROSS APPLY OPENJSON (S1.Model) 
                         WITH (Object NVARCHAR(MAX) '$.Object' AS JSON,
                               ID  INT '$.ID',
                               Name NVARCHAR(250) '$.Name') S2
                       CROSS APPLY OPENJSON (S2.Object) 
                         WITH (Task NVARCHAR(MAX) '$.Task' AS JSON ,
                               TaskName NVARCHAR(MAX) '$.TaskName') S3

JSON

来源：https://stackoverflow.com/questions/61572866/sql-query-to-get-the-json-array-values-by-comma-separated

1条答案

按热度按时间

j8ag8udp1#

已将json更正为有效格式，以下是所需输出的SQL查询。下面的查询在Oracle Compiler中编译并运行良好

SELECT * FROM JSON_TABLE(
    '{"Model":[{"ModelName":"Test Model","Object":[{"ID":1,"Name":"ABC","Task":[{"TaskID":1222,"Name":"TaskA"},{"TaskID":154,"Name":"TaskB"}]},{"ID":11,"Name":"ABCD","Task":[{"TaskID":222,"Name":"TaskX"},{"TaskID":234,"Name":"TaskY"}]}]}]}',
    '$.Model[*]'
    COLUMNS (
       -- L_MODEL_ROWNUM FOR ORDINALITY,
        L_MODEL_NAME VARCHAR2(10) PATH '$.ModelName',
        NESTED PATH '$.Object[*]' COLUMNS (
            --L_OBJECT_ROWNUM FOR ORDINALITY,
            L_OBJECT_ID NUMBER PATH '$.ID',
            L_OBJECT_NAME VARCHAR2(10) PATH '$.Name',
            L_TASK_NAME VARCHAR2(100) FORMAT JSON WITH WRAPPER PATH '$.Task[*].Name'
            )
        )
    )

以下是在SQL Server 2019上运行的查询，

DECLARE @JSONDATA NVARCHAR(MAX);
SET
   @JSONDATA = N'{"Model":[{"ModelName":"Test Model","Object":[{"ID":1,"Name":"ABC","Task":[{"TaskID":1222,"Name":"TaskA"},{"TaskID":154,"Name":"TaskB"}]},{"ID":11,"Name":"ABCD","Task":[{"TaskID":222,"Name":"TaskX"},{"TaskID":234,"Name":"TaskY"}]}]}]}';
WITH TASK AS 
(
   SELECT
      MODEL,
      ID,
      NAME,
      TASK_NAME 
   FROM
      OPENJSON(@JSONDATA) WITH (MODELS NVARCHAR(MAX) '$.Model' AS JSON)
      CROSS APPLY OPENJSON(MODELS) WITH (MODEL NVARCHAR(80) '$.ModelName', OBJECTS NVARCHAR(MAX) '$.Object' AS JSON) 
      CROSS APPLY OPENJSON(OBJECTS ) WITH (ID INT '$.ID', NAME NVARCHAR(250) '$.Name', TASKS NVARCHAR(MAX) '$.Task' AS JSON ) 
      CROSS APPLY OPENJSON (TASKS) WITH (TASK_NAME NVARCHAR(80) '$.Name') 
)
SELECT DISTINCT
   MODEL AS MODELNAME,
   ID AS OBJECTID,
   NAME AS OBJECTNAME,
   STUFF(( 
   SELECT
      ',' + [TASK_NAME] 
   FROM
      TASK T1 
   WHERE
      T1.[ID] = T2.[ID] FOR XML PATH('')), 1, 1, '') AS TASKNAME 
   FROM
      TASK T2;

赞(0）回复(0）举报 2023-05-30

我来回答

SQL查询，以逗号分隔json数组值

1条答案

相关问题

热门标签

最新问答