Question 247381 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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"
  }}
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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')) + '}'

  2. 0

    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'), '"')
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search