2

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
);
0
2

This is how I solved my problem:

SELECT
    descricao [nome],
    JSON_QUERY(endereco) [endereco]
FROM teste2
WHERE id=2
FOR JSON PATH, ROOT('clientes')

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.