Help with reading from JSON array

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  April 2, 2024 at 5:36 pm

  Go to Answer

  #4406542

  Hi all,

  I am trying to read the following JSON code in SQL Server:

  declare @json nvarchar(max)
  
  set @json = 
  '
  {
  "WorkId":121,
  "Code":"UK_AAT",
  "UpdateAttributes":
  {
  
  "TriggerDateMapping":
  {
  "3N":"N/A",
  "10N": "N/A"
  },
  
  "OverrideRules":
  [
  {"Busket Rule":"A1",
  "Exit_Window": 15,
  "Strategy": "STE Triggered",
  "Start_Date": "Month 2"
  },
  
  {"Busket Rule":"C2",
  "Exit_Window": 44,
  "Strategy": "STE",
  "Start_Date": "Month 2"
  },
  
  {"Busket Rule":"D1",
  "Exit_Window": 128,
  "Strategy": "Ignore",
  "Start_Date": "Q2"
  }
  
  ]
  
  }
  }
  '
  
  --select isjson(@json)
  
  select *
  from openjson(@json)
  with (
  WorkId int '$.WorkId',
  [Busket Rule] varchar(200) '$.UpdateAttributes.OverrideRules[0]."Busket Rule"',
  Exit_Window int '$.UpdateAttributes.OverrideRules[0].Exit_Window',
  Startegy varchar(200) '$.UpdateAttributes.OverrideRules[0].Strategy',
  Start_Date varchar(100) '$.UpdateAttributes.OverrideRules[0].Start_Date'
  )
  
  
  

  it reads correctly, but only for the 1st entry of array [index 0]. To reading all the rest, I have to manually change it to 1,2, and so on.  How I can read it so it will look like table?

  Thanks

   

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  April 2, 2024 at 5:53 pm

  Answer

  #4406546

  You need multiple OPENJSON functions.

  select j.WorkId, r.[Busket Rule], r.Exit_Window, r.Startegy, r.Start_Date
  from openjson(@json)
  with (
  WorkId int '$.WorkId',
  OverrideRules NVARCHAR(MAX) '$.UpdateAttributes.OverrideRules' AS JSON
  ) j
  CROSS APPLY OPENJSON(j.OverrideRules)
  WITH (
  [Busket Rule] varchar(200) '$."Busket Rule"',
  Exit_Window int '$.Exit_Window',
  Startegy varchar(200) '$.Strategy',
  Start_Date varchar(100) '$.Start_Date'
  ) AS r
  

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  April 2, 2024 at 6:58 pm

  #4406595

  Thanks a lot, Drew.

  It works as expected.