Create table from openjson(@json) results Azure SQL

Question

I am trying to make a table out of the [key] row values from a select * from openjson(@json) statement. The openjson(@json) statement gives me results that contains 53 [key] row values, here's a small snippet:

[key]      [value]                    [type]
_id        5b05390c5d222f0059209918   1
ean        65485555                   1
name       NULL                       0
holder     {"_id":"5b0538355d222f00585db6f1","name":"***... 5
root       {"_id":"5b05390c5d222f005920990a","holder":{"_id":"5b0538885... 5 
assigner   {"_id":"5b0538885d222f00570aca19","name":"***... 5
created    2018-05-23T09:49:00+0000   1
children   []                         4
address                               1
timezone   Etc/GMT-1                  1

I want a table that look something like this:

    table1
[_id]                    [ean]    [name] [holder]                  [etc...]
5b05390c5d222f0059209918 65485555 NULL   {"_id":"5b0538355d222...}

I also want to be able to insert values from another JSON into the same table

insert into table1 () 
select [value] from openjson(@json2)

Thank you!

score 0 · Answer 1 · answered Jun 07 '18 at 17:26

Just add a WITH clause to your OPENJSON query.

See OPENJSON, eg:

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-06-01T00:00:00"  
    },  
    "AccountNumber":"AW73565",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":3  
    }  
  }
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   varchar(200)   '$.Order.Number',  
              Date     datetime       '$.Order.Date',  
              Customer varchar(200)   '$.AccountNumber',  
              Quantity int            '$.Item.Quantity',  
              [Order]  nvarchar(MAX)  AS JSON  
 )

Create table from openjson(@json) results Azure SQL

1 Answers1