How to build JSON with selected fields from JSON columns in SQL Server keeping fields type

Question

I have JSON like this:

    {
  "_key": {
    "id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
    "rootId": "15c85327-9628-3685-b84a-375b546ba92a"
  },
  "employeeInfo": {
    "idNumber": "3",
    "gender": "Male",
    "active": true,
    "age": 20
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }
}

And I want to receive new JSON with desired fields. For some fields, I want to filter some inner fields (employeeInfo) and for some fields, I need to get all object. I'm using the next query:

SELECT
    '{ "employeeInfo": {"age: ' +
    JSON_VALUE(@json, '$.employeeInfo.age') + ', "active": ' +
    JSON_VALUE(@json, '$.employeeInfo.active') + ', "gender": ' +
    JSON_VALUE(@json, '$.employeeInfo.gender') + ' }, ' +
    '"product":' + JSON_QUERY(@json, '$.product') + '}' as info
FROM 
    item.[Item] AS c
INNER JOIN 
    (SELECT "rootId", MAX("revisionNo") AS maxRevisionNo 
     FROM item."Item"
     WHERE "rootId" = 'E3B455EF-D48E-338C-B6D4-FFD8B41243F9' 
     GROUP BY "rootId") AS subquery ON c."rootId" = subquery."rootId";

And I get response with such columns:

    { "employeeInfo": {"age: 20, "active": true, "gender": Male }, "product":{
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }}

But it seems JSON_VALUE doesn't return type, so 'Female' and 'Plan 1' are without quotes. I don't know what fields will be requested, so I can't add quotes myself. I will receive field names in runtime and want to build request dynamically. How can I execute a query to return values with their types? I.e. I expect the next response:

    { "employeeInfo": {"age: 20, "active": true, "gender": "Male" }, "product":{
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }}

I already have the solution for Postgres:

SELECT jsonb_strip_nulls(json_build_object('employee_info', json_build_object('age', c."info"->'employeeInfo' -> 'age', 'gender', c."info"->'employeeInfo' -> 'gender'), 'product', json_build_object('plan', c."info"->'product' -> 'plan'))::jsonb) as info ...

And also need to build a request for sql-server. I will build this request dynamically when I receive field names.

I'm using this SQL Server version:

Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1552 (ARM64)

UPD: I'm using next query for testing:

    DECLARE @json NVARCHAR(MAX) = '{
  "_key": {
    "id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
    "rootId": "15c85327-9628-3685-b84a-375b546ba92a"
  },
  "employeeInfo": {
    "idNumber": "3",
    "gender": "Male",
    "active": true,
    "age": 20
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }
}'

SELECT
        '{ "employeeInfo": {"age: ' +
        JSON_VALUE(@json, '$.employeeInfo.age') + ', "active": ' +
        JSON_VALUE(@json, '$.employeeInfo.active') + ', "gender": ' +
        JSON_VALUE(@json, '$.employeeInfo.gender') + ' }, ' +
        '"product":' + JSON_QUERY(@json, '$.product') + '}' as info

Actual result:

{ "employeeInfo": {"age: 20, "active": true, "gender": Male }, "product":{
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }}

Expected result:

{ "employeeInfo": {"age: 20, "active": true, "gender": "Male" }, "product":{
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }}

Answer 1

You can use FOR JSON PATH to rebuild the JSON.

From your previous question, it appears you don't know the data types, so you can modify my answer to include rebuilding employeeInfo. You need to use JSON_QUERY on it, otherwise you get double escaping.

$.product can be passed straight through without modification.

SELECT
  employeeInfo = JSON_QUERY((
    SELECT
      '{' +
      STRING_AGG(
        CONCAT(
          '"',
          STRING_ESCAPE(j.[key], 'json'),
          '":',
          IIF(j.type = 1, CONCAT('"', STRING_ESCAPE(j.value, 'json'), '"'), j.value)
        ),
        ','
      ) + '}'
    FROM OPENJSON(@json, '$.employeeInfo') j
    WHERE j.[key] IN ('age', 'gender', 'active')
  )),
  product = JSON_QUERY(@json, '$.product')
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

db<>fiddle

Answer 2

You could check if the returned value is a numeric or not :

CASE WHEN ISNUMERIC(JSON_VALUE(@json, '$.employeeInfo.gender')) = 1
            THEN JSON_VALUE(@json, '$.employeeInfo.gender')
            ELSE QUOTENAME(JSON_VALUE(@json, '$.employeeInfo.gender'), '"')

Answer 3

I have some solution, but maybe it can be improved? :

SELECT info = '{ ' +
                (SELECT CONCAT('"employeeInfo":{',
                               STRING_AGG(
                                       CONCAT('"', STRING_ESCAPE(j.[key], 'json'), '":',
                                              IIF(j.type = 1, CONCAT('"', STRING_ESCAPE(j.value, 'json'), '"'),
                                                  j.value)),
                                       ','),
                               '}')
                 FROM OPENJSON(@json, '$.employeeInfo') j
                 WHERE j.[key] IN ('age', 'gender', 'active')) + ', ' +

                (SELECT '"product":' + JSON_QUERY(@json, '$.product')) + '}'