Parse Data from OPENJSON()

Question

Here is my string:

SELECT * FROM OPENJSON ('{"data":{"CAD":1.3480102032}}')
WITH ( Rate VARCHAR(200) '$.data')

I need to extract the value 1.3480102032

Sorry, new to this! The result from what I get is NULL. Please help.

Object members can be accessed using dot-notation WITH (cad FLOAT '$.data.CAD') Docs — Daniel W., Commented Sep 22, 2023 at 17:40
I need to put this value into a SQL table. I tried WITH (cad FLOAT '$data.CAD') but it did not work. — Russ, Commented Sep 22, 2023 at 17:42
This is a daily exchange rate. I need to show the user in my Access front end what today's exchange rate is. — Russ, Commented Sep 22, 2023 at 17:43
K! I got it to work: select * from OPENJSON ('{"data":{"CAD":1.3480102032}}') WITH (Rate FLOAT '$.data."CAD"') — Russ, Commented Sep 22, 2023 at 17:47

Russ · Accepted Answer · 2023-09-23 14:06:57Z

1

    select * from OPENJSON ('{"data":{"CAD":1.3480102032}}')
WITH (Rate FLOAT '$.data."CAD"')

CAD needed to be put in quotes as it is a string.

answered Sep 22, 2023 at 17:48

Russ

33 bronze badges

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?
– Jeremy Caney
Commented Sep 23, 2023 at 1:29

Add a comment |

1 Answer 1