Convert simple JSON to SQL SELECT Statement

Question

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","Descri\u00e7\u00e3o":"FIV-005 3\u00baTRM19/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","Descri\u00e7\u00e3o":"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) '$."Descri\u00e7\u00e3o"',
    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?

Caius Jard · Accepted Answer · 2020-03-28 22:30:11Z

1

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

answered Mar 28, 2020 at 22:30

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

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

1 Comment

Caius Jard Over a year ago

No probs. Don't forget to click the grey tick under the vote numbers to turn it green if it's the answer to your problem, and upvote an answer (plus any others) if you found them useful

Collectives™ on Stack Overflow

Convert simple JSON to SQL SELECT Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related