如何在TSQL选择查询中将行转换为JSON? [英] How to convert rows to JSON in TSQL select query?

问题描述

以下查询将整个表作为单个JSON字符串(JSON对象列表-每行一个)返回:

Following query returns whole table as single JSON string (list of JSON objects - one per row):

SELECT * FROM MyTable FOR JSON AUTO 

我需要返回多行，每行将是表的行的JSON字符串.

I need to return multiple rows where each row will be a JSON string of a signle row of the table.

例如，如果表字段是A，B，C，则输出应为:

For example if table fields are A,B,C output should be:

{A: <value>, B: <value>, C: <value>}
{A: <value>, B: <value>, C: <value>}
{A: <value>, B: <value>, C: <value>}
...
{A: <value>, B: <value>, C: <value>}

如何在TSQL中执行此操作?

虽然可以通过串联字符串来手工构造JSON字符串，但我还是希望重用现有的工具来处理值中的引号之类的情况.

While it's possible to construct JSON strings by hand by concatenating strings, I'd prefer to reuse existing tool that would handle cases like quotes in values.

推荐答案

这将从select语句的行中返回json数据.

This will return json data in rows from a select statement.

DECLARE @json NVARCHAR(Max)
SET @json = (SELECT * FROM [Sales].[Customers] FOR JSON PATH, ROOT('data'))
SELECT value
FROM OPENJSON(@json,'$.data');

这篇关于如何在TSQL选择查询中将行转换为JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！