Pass JSON directly to SQL Server 2016

Question

I would like to pass an JSON object to a SQL Server 2016 stored procedure without deserializing the object and let the database process the data.

I'm passing in:

DECLARE @json NVarChar(2048) = N'[{
    "PMID": "31390573",
    "pubdate": "2019",
    "data": {
        "memberID": "1692",
        "pmid": "31390573",     
        "sortpubdate": "2019/08/06 00:00",
        "pmc": "PMC6733019"
    }
}, {
    "PMID": "31213464",
    "pubdate": "2019",
    "data": {
        "memberID": "1692",
        "pmid": "31213464",     
        "sortpubdate": "2019/08/15 00:00",
        "pmc": "PMC6753942"
    }
}, {
    "PMID": "31130363",
    "pubdate": "2019",
    "data": {
        "memberID": "1692",
        "pmid": "31130363",     
        "sortpubdate": "2019/06/20 00:00",
        "pmc": "PMC6588466"
    }
}]'

If I use

SELECT * FROM OPENJSON(@json);

I get

0   {   "PMID": "31390573",   "pubdate": "2019",   "data": {    "memberID": "1692",    "pmid": "31390573",      "sortpubdate": "2019/08/06 00:00",    "pmc": "PMC6733019"   }  }    5
1   {   "PMID": "31213464",   "pubdate": "2019",   "data": {    "memberID": "1692",    "pmid": "31213464",      "sortpubdate": "2019/08/15 00:00",    "pmc": "PMC6753942"   }  }    5
2   {   "PMID": "31130363",   "pubdate": "2019",   "data": {    "memberID": "1692",    "pmid": "31130363",      "sortpubdate": "2019/06/20 00:00",    "pmc": "PMC6588466"   }  }    5

I would like to end up with a table with one column for the items in the data element so a column for memberID -- pmid --sortpubdate--pmc.

memberID    pmid        sortpubdate
------------------------------------------------
1692        31390573    2019-08-06 00:00:00.0000000
1692        31213464    2019-08-15 00:00:00.0000000
1692        31130363    2019-06-20 00:00:00.0000000

Is this possible? If it was XML it could be ripped apart and processed.

score 0 · Answer 1 · answered Oct 04 '19 at 02:23

0

I found my answer and I'll leave it here in case some one else needs it

SELECT *  
FROM OPENJSON(@json)  
  WITH ( 
        memberID int '$.data.memberID', pmid int '$.data.pmid',  
         sortpubdate datetime2 '$.data.sortpubdate') 
END

answered Oct 04 '19 at 02:23

Bill

1,423
2
27
51

Pass JSON directly to SQL Server 2016

1 Answers1