Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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",
    ...
  }
}

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading