Nested JSON

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  October 22, 2018 at 12:22 pm

  #375579

  Hey
  I am trying to read JSON data into SQL.
  With a "flat" JSON file,no problem.
  SELECT * FROM OPENJSON(@json) WITH(id as int)

  But I got a nested JSON file, that is structured this way:

  id
  - stats
  --stat1
  --stat2
  --stat3
  id
  -stats
  --stat1
  --stat2
  --stat3

  How can I write an SQL, that will output that data in one row? Like this:
  id,stat1,stat2,stat3
  id,stat1,stat2,stat3

  Any help out here?

  Thanks you brilliant guys:)

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  October 23, 2018 at 6:27 am

  #2010748

  You can specify the structure of JSON in the With clause, something like:

  WITH
  (
  Id int N'id',
  Stat1 int N'stat.stat1'
  Stat2 int N'stat.stat2',
  Stat3 int N'stat.stat3'
  )