select one line and return it as a json array

Question

I have a table with quite a few columns. The question of whether it is alright is outside the scope of this question. I want to select one line (where pkValue=123) and return it as a json array.

[{"TableName":"mytable", "ColumnName":"myFirstCol","pkValue":123, "colValue":"myFirstValue", "colType":"myFirstType"},
...,
{"TableName":"mytable", "ColumnName":"myLastCol","pkValue":123, "colValue":"myLastValue", "colType":"myLastType"}]

I would want to do that without naming all columns in the query. It might be interesting to use the new json functions available in SQL Server 2022. I just can't figure out how.

The following query returns the metadata part of the result:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'

no it wouldn't, you have 4 columns in the select and 5 in every json of the array, begs the question what have you tried so far — nbk, May 16 '23 at 15:37
Does this answer your question? [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016) — nbk, May 16 '23 at 15:39
@nbk not sure how that's relevant, that's about how to do `JSON_AGG` in SQL Server (ie a bare array without an object wrapper) — Charlieface, May 16 '23 at 15:42

Charlieface · Answer 1 · 2023-05-16T16:03:00.313

0

It seems you want to query the columns from INFORMATION_SCHEMA and use a dynamic query.

it's not actually necessary if you know it at least has pkValue

SELECT t.*
FROM YourTable t
WHERE t.pkValue = 1
FOR JSON PATH;

if you really want a dynamic query, you can do something like this. Note that you should use sys.columns, as INFORMATION_SCHEMA is for compatibility use only.

DECLARE @sql nvarchar(max);
SELECT @sql = '
SELECT
  ' + STRING_AGG(QUOTENAME(c.name), ',
  '
) + '
FROM ' + QUOTENAME(@YourTable) ' t
WHERE ' + QUOTENAME(@ColumnFilter) + ' = @yourParam
FOR JSON PATH;
'
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
WHERE t.name = @YourTable;

PRINT @sql;

EXEC sp_executesql @sql,
  N'@yourParam bigint',
   @yourParam = @yourParam;

edited May 16 '23 at 16:03

answered May 16 '23 at 15:37

Charlieface

52,284
6
19
43

Each json object of the result I am trying to obtain has a column name (metadata) and the value for that column at pkValue=123. I don't see the latter in your post. Sorry if I was lacking clarity. – Ludovic Aubert May 16 '23 at 15:47
`INFORMATION_SCHEMA.COLUMNS` doesn't have a `pkValue` column, not sure what you mean. Do you mean: get the column names dynamically from `INFORMATION_SCHEMA` and create a dynamic query based on that? Why can't you just do `SELECT t.* FROM YourTable WHERE pkValue = 1 FOR JSON PATH` – Charlieface May 16 '23 at 15:53
possibly using SELECT * FROM myTable WHERE pkValue = 123 FOR JSON AUTO. It returns almost what I want. – Ludovic Aubert May 16 '23 at 16:00
see new edits.. – Charlieface May 16 '23 at 16:03

Ludovic Aubert · Accepted Answer · 2023-05-17T14:22:03.090

SELECT * FROM myTable
WHERE pkValue = 123
FOR JSON AUTO;

returns

tab=[
{"myFirstCol":"myFirstValue",
"mySecondCol":"mySecondValue",
...,
"myLastCol":"myLastValue"}
]

From there, using Javascript, you can do:

Object.entries(tab[0]).map(p => ({colName:p[0], colValue:p[1], pkValue:123, tableName:'myTable'}))

which turns tab into the result expected by Q.

[{"TableName":"mytable", "ColumnName":"myFirstCol","pkValue":123, "colValue":"myFirstValue", "colType":"myFirstType"},
...,
{"TableName":"mytable", "ColumnName":"myLastCol","pkValue":123, "colValue":"myLastValue", "colType":"myLastType"}]

select one line and return it as a json array

2 Answers2