CODEWITHSUNDEEP
jsonsql-serverdatabase
roger
roger

Reputation: 1263

How to extract JSON object inside SQL Server

I came across issue on extracting boolean or integer value from SQL Server and can't find any reference to it. Most of the examples or talks I came across are parsing JSON object and extract back as JSON. Maybe I didn’t understand it well, and does not understand SQL Server does casting SQL table into JSON in the background.

Recently I started to work on SQL Server, and I was ask to return data as JSON object. One of columns holds frequent change value and it can be an integer or boolean and the type for this column is NVARCHAR. We thought that when we extract it and convert to JSON by using SQL Server JSON AUTO key word and we would get our raw data back. But that was not the case, we get string back.

We try to store the data as JSON object

Insert in db_table(id, info) value (1, N’{a:true}’)

When we extract it

Select * from db_table ... JSON AUTO

Then we would get our JSON back

[{ id: 1, info: { a: true }}]

Instead we get { a: true } for info.

When we extract data with JSON, does it treat every value inside the table as string? How can I extract the original data?

Upvotes: 0

Views: 1833

Answers (1)

Aaron Hughes
Aaron Hughes

Reputation: 476

JSON objects in SQL are stored as strings. The concept of a document or JSON object doesn't really exist in SQL. The built in parsing logic is relatively new as well. Since you are storing the entire JSON object in a single column, rather than parsing out the values into individual columns, you have to select it as a JSON_QUERY upon returning it.

Also, in your example your JSON is invalid. the 'a' key needs to be properly quoted.

DECLARE @db_table TABLE (id int, info NVARCHAR(MAX))

INSERT INTO @db_table
VALUES
(1, N'{"a":true}')

SELECT id, JSON_QUERY(info) as info from @db_table FOR JSON AUTO

Upvotes: 2

Related Questions