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'

Answer 1

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"}]

Answer 2

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;