SQL returning empty JSON brackets when no value found instead of empty "value"

Question

I have a sql query which which will check a table called USER_PROFILE_ENTRY for STRING_VAL under a specific where clause as given below and set it for extension_locale

    Select extension_locale = (select STRING_VAL from USER_PROFILE_ENTRY
            where PROFILE_KEY = 'Culture' and ottr.USER_ID = USER_PROFILE_ENTRY.USER_ID)
            from USER_DATA ottr where USER_TYPE = 'U'
            FOR JSON PATH

the ottr.USER_ID in the where clause is from a table called USER_DATA so when you run below query and if there is no value which satifies this condition -> ottr.USER_ID = USER_PROFILE_ENTRY.USER_ID then nothing will be returned

select STRING_VAL from USER_PROFILE_ENTRY
            where PROFILE_KEY = 'Culture' and ottr.USER_ID = USER_PROFILE_ENTRY.USER_ID

But what I want is, if there is no value for above where condition, it should print extension_timezone = "" instead of not printing anything at all.

Below is output of above query :

[
  {},
  {
    "extension_locale": "en-US"
  },
  {},
  {
    "extension_locale": "en-US"
  },
  {
    "extension_locale": "en-GB"
  },
  {},
  {},
  {
    "extension_locale": "en-GB"
  },
  {
    "extension_locale": "en-GB"
  },
  {
    "extension_locale": "en-GB"
  }
]

So if you see the output, there are many {} (empty blocks) it means there was no satisfying where clause hence and empty output but what I want is like this :

[
  {
    "extension_locale": ""
  },
  {
    "extension_locale": "en-US"
  },
  {
    "extension_locale": "en-US"
  },
  {
    "extension_locale": "en-GB"
  },
  {
    "extension_locale": ""
   },
  {
    "extension_locale": ""
  },
  {
    "extension_locale": "en-GB"
  },
  {
    "extension_locale": "en-GB"
  },
  {
    "extension_locale": "en-GB"
  }
]

Can you post test data? Also, note that JSON support was introduced in SQL Server 2016, so the sql-server-2008 tag seems to be wrong. What is your actual SQL Server version? — Zhorov
– Zhorov, Commented Sep 29, 2020 at 6:54
What type of test data do you want? I have 2 tables, one is USER_DATA and other is USER_PROFILE_ENTRY, both tables have one common column, which is USER_ID which I am using in where clause — horizon
– horizon, Commented Sep 29, 2020 at 6:55
Not like this. Read the guidelines in the sql tag info and edit your question with proper sample data - and also, fix the wrong tag. Then I might be able to help you. — Zohar Peled
– Zohar Peled, Commented Sep 29, 2020 at 6:55
@ZoharPeled I have 2 tables, one is USER_DATA and other is USER_PROFILE_ENTRY, both tables have one common column, which is USER_ID which I am using in where clause. I hope I am able to make you understand — horizon
– horizon, Commented Sep 29, 2020 at 6:57

Zhorov · Accepted Answer · 2020-09-29 07:13:51Z

3

You have two options:

Use INCLUDE_NULL_VALUES option in the FOR JSON AUTO. In this case the result is an "extension_locale": null key\value pair in the generated output
Use COALESCE() to return the default value when the returned value from the inner SELECT statement is NULL. Then the result is an "extension_locale": "" key\value pair in the final JSON.

It's difficult without test data, but the following example, based on your code, is a possible option:

SELECT extension_locale = COALESCE(
   (
   SELECT STRING_VAL 
   FROM USER_PROFILE_ENTRY 
   WHERE PROFILE_KEY = 'Culture' AND ottr.USER_ID = USER_PROFILE_ENTRY.USER_ID
   ),
   ''
)
FROM USER_DATA ottr 
WHERE USER_TYPE = 'U'
FOR JSON PATH

answered Sep 29, 2020 at 7:13

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

horizon Over a year ago

It worked I was trying to user CASE for this but was not able to come to a solution. Marking this as answer

Collectives™ on Stack Overflow

SQL returning empty JSON brackets when no value found instead of empty "value"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related