T-SQL parse JSON array without property name in SQL Server

Question

Environment

SQL Server 2016
Windows 2016

I'm currently getting information from an API. When I call this API, I get the following JSON text back:

{"request":{"command":"series","series_id":"342"},"series":[{"series_id":"342","data":[["2023",125.39355],["2022",122.85654167],["2021",118.36935],["2020",113.74025],["2019",112.27875],["2018",110.3265],["2017",107.742],["2016",105.70325],["2015",104.6615],["2014",103.6625],["2013",101.76875],["2012",100.00025],["2011",98.15725],["2010",96.16425],["2009",95.019],["2008",94.39725],["2007",92.65325],["2006",90.2115],["2005",87.49],["2004",84.84175],["2003",82.63525],["2002",81.01275],["2001",79.8145],["2000",78.01625],["1999",76.27275],["1998",75.2195],["1997",74.39925],["1996",73.13225],["1995",71.819],["1994",70.342],["1993",68.87425],["1992",67.27775],["1991",65.77725],["1990",63.63575]]}]}'

Issue

I'm try to parse out the data array into two columns, 'Years' and 'Value'. I do not care about anything else. I'm not sure how to parse this Json string. My latest attempt was

 DECLARE @json nvarchar(max) = N'*<api data from above>*'

 SELECT  
     AllData --AllData,Replace(Replace(Replace(AllData, '"',''),'[',''),']','') 
 FROM 
     OPENJSON(@json)  
     WITH (
            data NVARCHAR(Max) '$.request.series.data' AS JSON
          )
 OUTER APPLY 
     OPENJSON(data)
          WITH (AllData VARCHAR(50) '$.request.series.data')

I've tried many different routes (e.g. $.data, $.series.data, etc) to no avail. Currently, if I could just get the data array back into a single column (e.g. 2023, 123.39.355; 2022, 122.85; etc) I could easily parse into two columns from there (hence the commented out Replace function), but all I get back is null.

Any ideas?

lptr · Accepted Answer · 2022-02-15 21:38:39Z

4

fiddle

select *
from openjson(@json, '$.series[0].data')
with
(
 year smallint '$[0]',
 value decimal(20, 10) '$[1]'
);

answered Feb 15, 2022 at 21:38

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL parse JSON array without property name in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related