Reputation: 69

Sql query for data stored as JSON

I have data stored in column as JSON, as shown below:

{"category":{"value":CAT, "demo":A.......

I want to query in SSMS and want an output like:

CAT

I tried "SELECT JSON_QUERY(TABLENAME,'$.CATEGORY') FROM TABLENAME" which gave result of the

{"value":CAT, "demo":A....... but I want only CAT. How do I do that?

Upvotes: 1

Answers (2)

Reputation: 413

Use JSON_VALUE to get specific values.

SELECT JSON_VALUE(TABLENAME,'$.CATEGORY.VALUE') AS Value

JSON_QUERY is for JSON fragments.

Upvotes: 3

Reputation: 473

You should use JSON_VALUE.

SELECT JSON_VALUE(ColumnName, '$.category.value') FROM TableName

Take note of the first parameter, it should be the column name, not the table name (as opposed to your example).

Upvotes: 2