Question 241026 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

jsonb_strip_nulls equivalent in sql server

I’m using next request to get json from some json type column in POSTGRES:

SELECT jsonb_strip_nulls(json_build_object(
        'employee', c."info"->'employee2', 'product', c."info"->'product2'
    )::jsonb) as info
FROM item."Item" AS c
WHERE "rootId" = '40a8cb67-84f8-33b5-87dc-e1b039858d43' GROUP BY "id") ...;

jsonb_strip_nulls omits unnecessary fields with null values. And I need to do the same in SQL server. I’m using next request to retrieve items:

SELECT '{ "employee": ' + JSON_QUERY(info, '$.employee') + ', "product": ' + JSON_QUERY(info, '$.product2') + ' }' as info
FROM item.[Item] AS c 
WHERE "rootId" = '3B319A76-A2C7-3DA1-8ECE-4736E4749261' GROUP BY "id") ...;

When I try to get some not existed field I receive nulls for all. But I need to omit fields that are present in JSON. For example, if product2 field doesn’t exist and ’employee’ exists I want to see employees in the response. How I can do It?

UPD: SQL server version:
Microsoft Azure SQL Edge Developer (RTM) – 15.0.2000.1552 (ARM64)

info content is json type field, something like:

{
  "_key": {
    "id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
    "rootId": "15c85327-9628-3685-b84a-375b546ba92a",
  },
  "employee": {
    "idNumber": "3",
    "gender": "M",
    ....
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "_type": "Product",
    ...
  }
}
Tags:

3

Answers


  1. 0

    Here’s how you can modify your query to achieve the desired outcome:

    SELECT 
    JSON_QUERY(
        JSON_MODIFY(
            JSON_MODIFY(info, '$.employee', JSON_QUERY(info, '$.employee')),
            '$.product',
            CASE WHEN JSON_VALUE(info, '$.product2') IS NOT NULL 
                 THEN JSON_QUERY(info, '$.product2') 
                 ELSE NULL 
            END
        ),
        '$'
    ) AS info
FROM item.[Item] AS c 
WHERE "rootId" = '3B319A76-A2C7-3DA1-8ECE-4736E4749261';
    Login or Signup to reply.
  2. 0

    You may try to build the expected JSON content with FOR JSON PATH and WITHOUT_ARRAY_WRAPPER modifier:

    SELECT info = (
  SELECT 
     employee = JSON_QUERY(info, '$.employee'), 
     product = JSON_QUERY(info, '$.product2')
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)  
FROM item

    On an Azure instance you may use JSON_OBJECT() (… I’m not sure if this feature is supported on Microsoft Azure SQL Edge Developer):

    SELECT JSON_OBJECT(
   'employee': JSON_QUERY(info, '$.employee'),
   'product': JSON_QUERY(info, '$.product2')
   ABSENT ON NULL
) AS info
FROM Item
    Login or Signup to reply.
  3. 0

    You can parse out the JSON and rebuild it using FOR JSON PATH inside a subquery. FOR JSON by default does not give null values.

    SELECT
  (
    SELECT
      j.*
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS info
FROM item.Item AS c 
CROSS APPLY OPENJSON(c.info)
  WITH (
    employee nvarchar(max) AS JSON,
    product2 nvarchar(max) AS JSON
  ) j
WHERE rootId = '3B319A76-A2C7-3DA1-8ECE-4736E4749261';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search