JSON from within a table

• DaveJenkins

  Mr or Mrs. 500

  Points: 568

  More actions

  January 25, 2017 at 12:35 pm

  #317841

  Hi there,

  I can successfully parse JSON from a file based on some examples that are out there. However, I'm having a major brain failure when it comes to parsing the data that is contained within a table.

  The JSON is stored like this:
  
[{"format":"CD1","Valid":"Yes"},{"format":"CD2","Valid":"Yes"},{"format":"CD2","Valid":"No"}]


  Any pointers would be appreciated
  Regards,

  Dave

• DaveJenkins

  Mr or Mrs. 500

  Points: 568

  More actions

  January 25, 2017 at 12:54 pm

  #1924954

  So, I've kept on going and found JSON_Value, which I think is the key. I only seem to be able to return NULLs as I am not sure how to access the array, I've tried:

  JSON_Value(c.MyJson, '$.format')

  ... and variations on that. However, I' sure that it needs something like $.XXX.format.

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  January 25, 2017 at 1:27 pm

  #1924956

  DaveJenkins - Wednesday, January 25, 2017 12:54 PM

  So, I've kept on going and found JSON_Value, which I think is the key. I only seem to be able to return NULLs as I am not sure how to access the array, I've tried:

  JSON_Value(c.MyJson, '$.format')

  ... and variations on that. However, I' sure that it needs something like $.XXX.format.

  Have a look at this & see whether it's of any use:

  
IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
  DROP TABLE #tmp;

  CREATE TABLE #tmp (Jason VARCHAR(500));

  INSERT #tmp
(
  Jason
)
VALUES ('[{"format":"CD1","Valid":"Yes"},{"format":"CD2","Valid":"Yes"},{"format":"CD2","Valid":"No"}]');

  SELECT *
FROM #tmp t
  CROSS APPLY
  OPENJSON(t.Jason)
  WITH
  (
   format VARCHAR(50),
   Valid VARCHAR(10)
  ) CDArray;


  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• DaveJenkins

  Mr or Mrs. 500

  Points: 568

  More actions

  January 25, 2017 at 3:12 pm

  #1924975

  Phil Parkin - Wednesday, January 25, 2017 1:27 PM

  DaveJenkins - Wednesday, January 25, 2017 12:54 PM

  So, I've kept on going and found JSON_Value, which I think is the key. I only seem to be able to return NULLs as I am not sure how to access the array, I've tried:

  JSON_Value(c.MyJson, '$.format')

  ... and variations on that. However, I' sure that it needs something like $.XXX.format.

  Have a look at this & see whether it's of any use:

  
IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
  DROP TABLE #tmp;

  CREATE TABLE #tmp (Jason VARCHAR(500));

  INSERT #tmp
(
  Jason
)
VALUES ('[{"format":"CD1","Valid":"Yes"},{"format":"CD2","Valid":"Yes"},{"format":"CD2","Valid":"No"}]');

  SELECT *
FROM #tmp t
  CROSS APPLY
  OPENJSON(t.Jason)
  WITH
  (
   format VARCHAR(50),
   Valid VARCHAR(10)
  ) CDArray;


  :Whistling:

  :Whistling: :Whistling: :Whistling:
  Yes, that was spot on. MASSIVE brain failure, now you've done that I'm looking back and kicking myself!!

  Thank you for the assistance. 

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 25, 2017 at 11:22 pm

  #1925001

  Quick (obvious) thought if the JSON column is NULL then you may want to use OUTER APPLY
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
DROP TABLE #tmp;

  CREATE TABLE #tmp (ID INT IDENTITY(1,1), Jason VARCHAR(500));

  INSERT #tmp
(
Jason
)
VALUES ('[{"format":"CD1","Valid":"Yes"},{"format":"CD2","Valid":"Yes"},{"format":"CD2","Valid":"No"}]'),(NULL);

  SELECT
  T.ID
 ,X.[format]
 ,X.Valid
FROM #tmp t
CROSS APPLY
OPENJSON(t.Jason)
WITH
(
  format VARCHAR(50),
  Valid VARCHAR(10)
) X;

  SELECT
  T.ID
 ,X.[format]
 ,X.Valid
FROM #tmp t
OUTER APPLY
OPENJSON(t.Jason)
WITH
(
  format VARCHAR(50),
  Valid VARCHAR(10)
) X;

  Output
  ID          format     Valid
----------- ---------- ----------
1           CD1        Yes
1           CD2        Yes
1           CD2        No

  ID          format     Valid
----------- ---------- ----------
1           CD1        Yes
1           CD2        Yes
1           CD2        No
2           NULL       NULL

  

• DaveJenkins

  Mr or Mrs. 500

  Points: 568

  More actions

  January 26, 2017 at 12:26 am

  #1925007

  Eirikur Eiriksson - Wednesday, January 25, 2017 11:22 PM

  Quick (obvious) thought if the JSON column is NULL then you may want to use OUTER APPLY
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
DROP TABLE #tmp;

  CREATE TABLE #tmp (ID INT IDENTITY(1,1), Jason VARCHAR(500));

  INSERT #tmp
(
Jason
)
VALUES ('[{"format":"CD1","Valid":"Yes"},{"format":"CD2","Valid":"Yes"},{"format":"CD2","Valid":"No"}]'),(NULL);

  SELECT
  T.ID
 ,X.[format]
 ,X.Valid
FROM #tmp t
CROSS APPLY
OPENJSON(t.Jason)
WITH
(
  format VARCHAR(50),
  Valid VARCHAR(10)
) X;

  SELECT
  T.ID
 ,X.[format]
 ,X.Valid
FROM #tmp t
OUTER APPLY
OPENJSON(t.Jason)
WITH
(
  format VARCHAR(50),
  Valid VARCHAR(10)
) X;

  Output
  ID          format     Valid
----------- ---------- ----------
1           CD1        Yes
1           CD2        Yes
1           CD2        No

  ID          format     Valid
----------- ---------- ----------
1           CD1        Yes
1           CD2        Yes
1           CD2        No
2           NULL       NULL
='font-size:9.0pt;mso-bidi-font-size:11.0pt;line-height:107%;font-family:"courier>
  

  😀
  Doh! Think the problem is, I missed all the obvious things with this one even though they were staring me in the face.

  But yes, this works really well! Thank you Eirikur.