0

i am getting this error in ssms while using openjson function

JSON text is not properly formatted. Unexpected character '1' is found at position 0.

Below is my code.

   DECLARE @json NVARCHAR(MAX)
    set @json='{"versionId":1609921210,"companyId":36773386,"companyNote":"Company Note","filingDate":"03-10-2022 04:54:00.000","collectionEntityToCollectionStageId":491414859,"userId":924446069,"feedFileId":1,"periods":[{"estimatePeriodId":-1,"periodTypeId":1,"calendarYear":2020,"calendarQuarter":4,"fiscalChainSeriesId":0,"fiscalQuarter":4,"fiscalYear":2020,"periodEndDate":"2020/12/31","displayOrder":1,"Operation":"Insert"},{"estimatePeriodId":-2,"periodTypeId":1,"calendarYear":2021,"calendarQuarter":4,"fiscalChainSeriesId":1,"fiscalQuarter":4,"fiscalYear":2021,"periodEndDate":"2021/12/31","displayOrder":2,"Operation":"Insert"}],"estimatedetails":[{"estimateDetailId":-1,"estimatePeriodId":-1,"researchContributorId":3169,"parentFlag":1,"accountingStandardId":3,"tradingItemId":132856177,"flavorTypeId":0,"auditComment":"this is comment","Operation":"Insert","dataitems":[{"dataid":-1,"auditTypeId":286,"dataItemId":100186,"dataItemValue":1111,"Notes":"this is note","units":"Millions","currency":"KRW","estimateSplitInfoId":1320317,"dataCollectionTypeGroupId":1,"pageNo":7,"x0":406,"y0":523,"x1":580,"y1":515,"Operation":"Insert"},{"dataid":-2,"auditTypeId":286,"dataItemId":100182,"dataItemValue":2222,"Notes":"this is note","units":"Millions","currency":"KRW","estimateSplitInfoId":2,"dataCollectionTypeGroupId":1,"pageNo":7,"x0":406,"y0":523,"x1":580,"y1":515,"Operation":"Insert"},{"dataid":-3,"auditTypeId":286,"dataItemId":100182,"dataItemValue":2222,"Notes":"this is note","units":"Millions","currency":"KRW","estimateSplitInfoId":3,"dataCollectionTypeGroupId":1,"pageNo":7,"x0":406,"y0":523,"x1":580,"y1":515,"Operation":"Insert"}]}]}'

 SELECT 
 JSON_Value (a.value, '$.versionId') as versionId,
 JSON_Value (b.value, '$.estimateDetailId') as estimateDetailId,
 JSON_Value (b.value, '$.estimatePeriodId') as estimatePeriodId,
 JSON_Value (b.value, '$.researchContributorId') as researchContributorId,
 JSON_Value (c.value, '$.dataid') as dataid,
 JSON_Value (c.value, '$.auditTypeId') as auditTypeId,
 JSON_Value (c.value, '$.dataItemId') as dataItemId,
 JSON_Value (c.value, '$.dataItemValue') as dataItemValue,
 JSON_Value (c.value, '$.Notes') as Notes
 FROM OPENJSON(@json) as a
 CROSS APPLY OPENJSON(a.value,'$.estimatedetails') b
 CROSS APPLY OPENJSON(b.value,'$.dataitems') c

What wrong with my json or code?

Thom A
  • 88,727
  • 11
  • 45
  • 75
shashank
  • 85
  • 8
  • At times like this, formatting your JSON would really help (you). – Thom A Sep 26 '22 at 18:51
  • try commenting most of it out and using 'select *' to understand what you are getting. Select * FROM OPENJSON(@json) as a - statement should start you on your way to seeing what each part of the statement is doing. You might show what your desired result is too. – Scott Mildenberger Sep 26 '22 at 19:08
  • This might be useful for you to see also: SELECT JSON_Value (@json, '$.versionId') as versionId – Scott Mildenberger Sep 26 '22 at 19:16
  • Try stripping your query down to the bare minimum and start from basics, e.g.: if you `SELECT * FROM OPENJSON(@json) as a;` you get a result table with three columns `key`, `value` and `type`. What happens now when you `SELECT JSON_Value ('1609921210', '$.versionId') as versionId`? – AlwaysLearning Sep 26 '22 at 22:03
  • Have you tried reading the `OPENJSON()` documentation yet? Especially the [`with_clause` section](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql#with_clause-1)? – AlwaysLearning Sep 27 '22 at 00:12

0 Answers0