How to build new JSON with JSON_VALUE in SQL Server? Can't set value 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",
    "age": 20,
    ....
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "_type": "Product",
    ...
  }
}

And I want to receive new JSON with desired fields. I'm using next query:

SELECT 
    '{ "employeeInfo": {"age: ' + 
    JSON_VALUE(info, '$.employeeInfo.age') + ', "gender": ' + 
    JSON_VALUE(info, '$.employeeInfo.gender') + ' }' 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 this response:

{ "employeeInfo": {"age: 38, "gender": Female }

But it seems JSON_VALUE doesn't return type, so Female is without quotes. I don't know what fields will be requested, so I can't add quotes myself. How can I execute a query to return values with their types. I.e. I expect next response:

{ "employeeInfo": {"age: 38, "gender": "Female" }

I'm using this SQL Server version:

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

UPD: I'm already have 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', c."info"->'product')::jsonb) as info ...

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

UPD2: I've created one more question which contains additional requirements. I need "product.plan" field also and have possibility to add any other fields How to build JSON with selected fields from JSON columns in SQL server keeping fields type

Answer 1

You may try to build the expected JSON with FOR JSON PATH and dot-separated column names for nested content, not with string concatenation. The documentation explains how FOR JSON converts SQL Server data types to JSON data types:

• SQL Server character and string types (char, nchar, varchar, nvarchar) are converted to string JSON string data type.
• SQL Server numeric types (int, bigint, float, decimal, numeric) are converted to JSON number data type.

Note, that with the additional INCLUDE_NULL_VALUES modifier, you may include the possible NULL values in the generated JSON.

SELECT 
   info = (
      SELECT 
         JSON_VALUE(info, '$.employeeInfo.age') AS "employeeInfo.age",
         JSON_VALUE(info, '$.employeeInfo.gender') AS "employeeInfo.gender"
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
   )
FROM ...

JSON_VALUE() always returns nvarchar(4000) and FOR JSON converts the values as JSON strings. If you need the actual data types, a solution is an explicit convertion with CONVERT() or an OPENJSON() call with explicit schema with actual columns data types.

SELECT 
   info = (
      SELECT age AS "employeeInfo.age", gender AS "employeeInfo.gender"
      FROM OPENJSON (info) WITH (
         age int '$.employeeInfo.age',
         gender varchar(10) '$.employeeInfo.gender'
      )
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
   )
FROM ...

SQL Server and Azure have limited JSON capabilities. If you do not know the actual data types of the referenced JSON properties, you need to call OPENJSON() with default schema and analyze the result (a table with columns key, value and type). A possible approach, parsing only the $.employeeInfo part of the stored JSON, is the following statement:

SELECT 
   info = (
      SELECT CONCAT('{"employeeInfo": {', STRING_AGG(t.Pair, ',') ,'}}') 
      FROM (
         SELECT 
            CONCAT(
               '"', [key], '":', 
               CASE 
                  WHEN [type] = 0 THEN 'null' 
                  WHEN [type] = 1 THEN CONCAT('"', STRING_ESCAPE([value], 'json'), '"') 
                  WHEN [type] = 2 THEN [value] 
                  WHEN [type] = 3 THEN [value] 
                  WHEN [type] = 4 THEN JSON_QUERY([value]) 
                  WHEN [type] = 5 THEN JSON_QUERY([value]) 
               END
            ) AS Pair 
         FROM OPENJSON (info, '$.employeeInfo')
         WHERE [key] IN ('age', 'gender') 
      ) t
   )
FROM ...

Answer 2

A slightly better syntax for @zhorov's excellent answer.

• Use a ROOT parameter instead of adding it to the column name.
• Put $.employeeInfo directly into OPENJSON
• Use a window function instead of a self-join

SELECT 
   info = (
      SELECT
        j.age,
        j.gender
      FROM OPENJSON(i.info, '$.employeeInfo')
        WITH (
          age sql_variant,
          gender varchar(10)
        ) j
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, ROOT('employeeInfo') 
   )
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY i.rootId ORDER BY i.revisionNo DESC)
    FROM item.Item AS i
    WHERE i.rootId = 'E3B455EF-D48E-338C-B6D4-FFD8B41243F9' 
) i
WHERE i.rn = 1;

If you want to do it completely dynamically then you need OPENJSON without a schema. Then rebuild it using a combination of STRING_AGG CONCAT and STRING_ESCAPE.

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(i.info, '$.employeeInfo') j
    WHERE j.[key] IN ('age', 'gender')
  )
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY i.rootId ORDER BY i.revisionNo DESC)
    FROM item.Item AS i
    WHERE i.rootId = 'E3B455EF-D48E-338C-B6D4-FFD8B41243F9' 
) i
WHERE i.rn = 1;