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