I'm learning SQL Server 2016, I learned how to extract JSON from tables.
Now I need to store JSON in a column. My JSON was:
{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}
I`m using nvarchar as the column type, but when I store it using:
UPDATE test2 SET endereco = '{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}'
When I do a select, it shows like this:
{\"endereço\":\"R. Nome da Rua, 000\", cep:\"00000-000\"}
But I was expecting this:
{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}
What was I supposed to do?
My test table creation script:
CREATE TABLE [dbo].[teste2]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[descricao] [varchar](200) NULL,
[endereco] [nvarchar](MAX) NULL
);
Best Answer
This is how I solved my problem:
The "magic" was performed by
JSON_QUERY (expression, path )
as documented here:JSON_QUERY (Transact-SQL)
See also:
Frequently Asked Questions about JSON in SQL Server