Parsing JSON stored in sqlserver object

Asked Aug 08 '18 at 12:51

Active Aug 21 '18 at 13:57

Viewed 3,621 times

I am trying to retrieve values from a json string stored in an sqlserver object using SQL. this is what I have

SELECT JSON_VALUE([JSOn_Field], '$.name')
FROM users

I am getting this error:

Argument data type text is invalid for argument 1 of json_value function.

edited Aug 21 '18 at 13:57

Andrea

asked Aug 08 '18 at 12:51

davCantor

2

Why are you using Text instead of NVarchar(max)? – David Browne - Microsoft Aug 08 '18 at 12:58
4

You probably need to cast the field: `cast([JSOn_Field] as nvarchar(max))` – vnov Aug 08 '18 at 13:03
how do I do that inside an sql query? – davCantor Aug 08 '18 at 13:07
7

`SELECT JSON_VALUE(cast([JSOn_Field] as nvarchar(max)), '$.name') FROM users` – vnov Aug 08 '18 at 13:09
Thank you this is great it worked!! – davCantor Aug 08 '18 at 13:14
How do I use the same sql query for a JSON array ? this is what I have SELECT JSON_Query([JSON_Field], '$contactDetails.name') FROM users I am getting all null values – davCantor Aug 09 '18 at 10:00
Its not clear what you want and what is the structure of your JSON. But I think you need to use table function OPENJSON, like in https://stackoverflow.com/questions/45426043/select-results-from-multiple-array-elements-in-json-document-with-sql-server-201 – vnov Aug 10 '18 at 12:21

0 Answers0