A concatenation OPENJSON in a SQL Server 2016 stored procedure

Question

I need to combine all of the authors for a particular UID. The basic fields are working from the code at another post.

DECLARE @json NVARCHAR(MAX)

SET @json = '{
        "header": {
            "type": "esummary",
            "version": "0.3"
        },
        "result": {
            "uids": [
                "17784783",
                "19505939",
                "30166592"
            ],
            "17784783": {
                "uid": "17784783",
                "pubdate": "2007 Aug",
                "epubdate": "2007 Jul 20",
                "source": "PLoS Comput Biol",          
                "sortpubdate": "2007/08/01 00:00",
                   "authors": [
                {
                    "name": "Yu Y",
                    "authtype": "Author",
                    "clusterid": ""
                },
                {
                    "name": "Wang G",
                    "authtype": "Author",
                    "clusterid": ""
                },
                {
                    "name": "Simha R",
                    "authtype": "Author",
                    "clusterid": ""
                }
                 ]          
            ,
            "19505939": {
                "uid": "19505939",
                "pubdate": "2009 Aug 1",
                "epubdate": "2009 Jun 8",
                "source": "Bioinformatics",          
                "sortpubdate": "2009/08/01 00:00"
                  },
                "authors": [
                {
                    "name": "Zang C",
                    "authtype": "Author",
                    "clusterid": ""
                }],

        "30166592": {
            "uid": "30166592",
            "pubdate": "2019 Jan",
            "epubdate": "2018 Aug 30",
            "source": "Oncogene",
             "sortpubdate": "2019/01/01 00:00",
            "authors": [
            {
                "name": "Sun J",
                "authtype": "Author",
                "clusterid": ""
            },
            {
                "name": "Cai X",
                "authtype": "Author",
                "clusterid": ""
            }],

        }
    }
}'

and I would like to end up with

uid         sortpubdate         epubdate         Authors
-----------------------------------------------------------------------
17784783    2007/08/01 00:00    2007 Jul 20      Yu Y,Wang G,Simha R
19505939    2009/08/01 00:00    2009 Jun 8       Simha R   
30166592    2019/01/01 00:00    2018 Aug 30      Sun J, Cai, X

I got a great answer from Zohar Peled at OPENJSON syntax in a stored procedure in SQL Server 2016 that help with the first part now I need to see if I can finish this up.

SELECT [uid], [sortpubdate], [epubdate]
FROM OPENJSON(@json, N'$.result') AS items
CROSS APPLY
-- parse each object in the array
OPENJSON(items.[value])
WITH(
    [uid] nvarchar(max) N'$.uid' ,
    [sortpubdate] nvarchar(max) N'$.sortpubdate',
    [epubdate] nvarchar(max) N'$.epubdate'
) As content
WHERE [key] <> 'uids' -- Get only the relevant content

Basically, you can use the same technique (cross apply to another openjson) to get the authors names for each `uid` and a combination of [`stuff` and `for xml`](https://stackoverflow.com/questions/41787834/get-a-comma-delimited-string-from-rows/41788610#41788610) to stimulate `string_agg` (which was introduced, fashionably late, only on 2017 version). — Zohar Peled, Aug 26 '19 at 14:20
Something like SELECT stuff(( SELECT '', '' + cast(name as varchar(max)) FROM @json FOR XML PATH('''') ), 1, 2, '''') — Bill, Aug 26 '19 at 15:15
Not exactly - you need to first extract the names from the json by using another `openjson` (with yet another `cross apply`) - the result of that cross apply should be concatenated into a comma delimited string for each row in the main query. — Zohar Peled, Aug 26 '19 at 15:17
Sorry- sounds over my head. I've been Googling and can't find an answer — Bill, Aug 26 '19 at 15:30

Zohar Peled · Accepted Answer · 2019-08-27T06:31:45.917

The json you've posted is a bit messy, so I've edited it.

This next query should get you the results including the author names using a common table expression and a combination of stuff and for xml.

Fixed json:

DECLARE @json NVARCHAR(MAX) = 
'{
  "header": {
    "type": "esummary",
    "version": "0.3"
  },
  "result": {
    "17784783": {
      "uid": "17784783",
      "pubdate": "2007 Aug",
      "epubdate": "2007 Jul 20",
      "source": "PLoS Comput Biol",
      "sortpubdate": "2007/08/01 00:00",
      "authors": [
        {
          "name": "Yu Y",
          "authtype": "Author",
          "clusterid": ""
        },
        {
          "name": "Wang G",
          "authtype": "Author",
          "clusterid": ""
        },
        {
          "name": "Simha R",
          "authtype": "Author",
          "clusterid": ""
        }
      ]
    },
    "19505939": {
      "uid": "19505939",
      "pubdate": "2009 Aug 1",
      "epubdate": "2009 Jun 8",
      "source": "Bioinformatics",
      "sortpubdate": "2009/08/01 00:00",
      "authors": [
        {
          "name": "Zang C",
          "authtype": "Author",
          "clusterid": ""
        }
      ]
    },
    "30166592": {
      "uid": "30166592",
      "pubdate": "2019 Jan",
      "epubdate": "2018 Aug 30",
      "source": "Oncogene",
      "sortpubdate": "2019/01/01 00:00",
      "authors": [
        {
          "name": "Sun J",
          "authtype": "Author",
          "clusterid": ""
        },
        {
          "name": "Cai X",
          "authtype": "Author",
          "clusterid": ""
        }
      ]
    },
    "uids": [
      "17784783",
      "19505939",
      "30166592"
    ]
  }
}';

Use a common table expression to extract the values from the json:

WITH CTE AS
(
    SELECT  [uid], 
            [sortpubdate], 
            [epubdate], 
            [name]
    FROM OPENJSON(@json, N'$.result') AS items
    CROSS APPLY
    -- parse each object in the array
    OPENJSON(items.[value])
    WITH(
        [uid] nvarchar(max) N'$.uid' ,
        [sortpubdate] nvarchar(max) N'$.sortpubdate',
        [epubdate] nvarchar(max) N'$.epubdate',
        -- Note the AS JSON on the next row - will not work without it!
        [authors] nvarchar(max) N'$.authors'  AS JSON 
    ) As content
    CROSS APPLY 
    OPENJSON([authors])
    WITH ([name] nvarchar(max) N'$.name') 
    As authorsNames
    WHERE items.[key] <> 'uids' -- Get only the relevant content
)

And query the cte with stuff and for xml:

SELECT  DISTINCT [uid], 
        [sortpubdate], 
        [epubdate], 
        STUFF((
            SELECT ',' + [name] 
            FROM CTE As t1
            WHERE t1.[uid] = t0.[uid]
            FOR XML PATH('')
        ), 1, 1, '') As authors
FROM CTE As t0

Results:

uid         sortpubdate         epubdate        authors
17784783    2007/08/01 00:00    2007 Jul 20     Yu Y,Wang G,Simha R
19505939    2009/08/01 00:00    2009 Jun 8      Zang C
30166592    2019/01/01 00:00    2018 Aug 30     Sun J,Cai X

I've edited my answer to give you a complete solution. – Zohar Peled Aug 27 '19 at 06:32 — Zohar Peled, Aug 27 '19 at 06:32
This is a above and beyond effort – Bill Aug 27 '19 at 12:03 — Bill, Aug 27 '19 at 12:03

A concatenation OPENJSON in a SQL Server 2016 stored procedure

1 Answers1

Linked