Convert simple JSON to SQL SELECT Statement

I have the following JSON:

DECLARE @json NVARCHAR(MAX)

SET @json = N'[
  {"@odata.context":"http://www.example.com","value":[
  {"financialmovements_ID":1,"Data":"2020-02-10T00:00:00Z","ES":"E","Descriu00e7u00e3o":"FIV-005 3u00baTRM19/20","Valor":455.9700,"ActGlbActDescr":"Reg. Financ. Pag.","ActGlbContr":"FIV005","ActGlbContrDescr":"Cond FIV-005"},
  {"financialmovements_ID":2,"Data":"2019-11-14T00:00:00Z","ES":"E","Descriu00e7u00e3o":"Pag. Cond FIV005","Valor":1958.6600,"ActGlbActDescr":"Reg. Financ. Pag.","ActGlbContr":"FIV005","ActGlbContrDescr":"Cond FIV-005"}]}
]'

I’m trying to convert to SQL as follows:

SELECT *
FROM OPENJSON(@json, '$.value')
WITH (
    --financialmovements_ID INT '$.financialmovements_ID',
    Data DATETIME2 '$.Data',
    [E/S] NVARCHAR(max) '$.ES',
    DescTrs NVARCHAR(max) '$."Descriu00e7u00e3o"',
    Valor MONEY '$.Valor',
    ActGlbActDescr NVARCHAR(max) '$.ActGlbActDescr',
    ActGlbContr NVARCHAR(max) '$.ActGlbContr',
    ActGlbContrDescr NVARCHAR(max) '$.ActGlbContrDescr'
);

All i get is an empty resultset (0 rows when it should be 2 rows), what am i doing wrong?

Answer

Your JSON has square brackets round the whole thing, meaning it is an array (it only has one element) so your OPENJSON starting point will have to be $[0].value

Advertisement

Answer