SQL 2016 OPENJSON Array

• rka

  SSCommitted

  Points: 1599

  More actions

  August 5, 2016 at 4:58 pm

  #400530

  Hi,

  I have a mix of data in a table as follows:

  person_id interests

  1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp{"team sports": ["soccer", "rugby", "basketball"], "individual sports": ["table tennis", "chess", "dart", "shooting"], "others": "fishing"}

  I am trying to achieve the results as follows:

  person_id&nbsp&nbspsport_type&nbsp&nbsp&nbsp&nbsp&nbsp&nbspinterests

  1 &nbsp&nbsp&nbsp&nbsp team sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp soccer

  1 &nbsp&nbsp&nbsp&nbsp team sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp rugby

  1 &nbsp&nbsp&nbsp&nbsp team sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp basketball

  1 &nbsp&nbsp&nbsp&nbsp individual sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp table tennis

  1 &nbsp&nbsp&nbsp&nbsp individual sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp chess

  1 &nbsp&nbsp&nbsp&nbsp individual sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp dart

  1 &nbsp&nbsp&nbsp&nbsp individual sports &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp shooting

  1 &nbsp&nbsp&nbsp&nbsp others &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp fishing

  Can someone help me how to solve this problem as I am new to this OPENJSON query in SQL 2016

  Thanks

• rka

  SSCommitted

  Points: 1599

  More actions

  August 5, 2016 at 5:34 pm

  #1894167

  I wrote this query but it is not returning entire results. It is failing when TYPE is not an array e.g. Fishing in this instance as it is not JSON anymore after first split.

  Declare @json nvarchar(max)

  Set @json = '{

  "team sport": ["soccer", "rugby", "basketball"],

  "individual sports": ["table tennis", "chess", "dart", "shooting"],

  "others": "fishing"

  }';

  SELECT *

  FROM OPENJSON(@json, 'lax $') main

  CROSS APPLY OPENJSON(main.value)

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  August 7, 2016 at 8:18 pm

  #1894259

  Any chance of changing the input to make that ["fishing"]?

  The error message is that the input is not properly formatted. Making it a one-element array fixes the problem.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills