Sql server open json

• sathwik.em91

  SSC Eights!

  Points: 878

  More actions

  January 20, 2021 at 8:36 pm

  #3835641

  DECLARE @JsonData NVARCHAR(MAX) =N'[{"preview":true,"offset":0,"result":{"pkid":"3e1900e5-e997-4ae2-92ee-83647de15a0c","duration":"20","callingPartyNumber":"7736773234"}}

  ,{"preview":true,"offset":1,"result":{"pkid":"b0805ec0-65ba-47ee-b5bc-81cf08dc9a9e","duration":"0","callingPartyNumber":"13789000"}}

  ]

  '

  SELECT

  root. AS [Order]

  , TheValues., TheValues.[value]

  , resultval. , resultval.[value]

  FROM OPENJSON ( @jsondata ) AS root

  CROSS APPLY OPENJSON ( root.value) AS TheValues

  CROSS APPLY OPENJSON(TheValues.value ) AS resultval

  I have the following JSON array I wanted to parse out all the key value pairs in this.my end goal is to get three column values

  PKID        duration       callingPartynumber

  I have the code below but it is throwing me an error:JSON text is not properly formatted. Unexpected character 't' is found at position 0.

  I am looking for solutions  without using  the WITH operator since the table schema will not be the same.

  Any help would be really appreciated.Let me know if you have any questions.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 21, 2021 at 9:05 am

  #3835796

  See if this helps

  SELECT
  root.[key] AS [Order]
  , resultval.[key] , resultval.[value]
  FROM OPENJSON ( @jsondata ) AS root
  CROSS APPLY OPENJSON ( root.value,  '$.result') AS resultval

  • This reply was modified 3 years, 10 months ago by  Mark Cowne. Reason: Simplified code
  • This reply was modified 3 years, 10 months ago by  Mark Cowne.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537