CODEWITHSUNDEEP
sql-serverdatabaset-sqlunion
Metal Wing
Metal Wing

Reputation: 1175

Renaming a JSON column for a UNION

Remark: my example is overly simplified. In reality, I am dealing with a huge query. But to illustrate the issue/errors, let us resort to apples and oranges.

My original query looked like this:

SELECT 'FruitsCount' AS "Type", (SELECT count(id) as Counter, [Name] FROM Fruits group by name FOR JSON PATH) AS "Value"

Which would result in something like. Let's refer to this as Format A

|---------------------|------------------------------------------------------------------------------|
|        Type         |                                   Value                                      |
|---------------------|------------------------------------------------------------------------------|
|     FruitCount      |         [{"Counter":2, "Name":"Apple"},{"Counter":3, "Name":"Orange"}]       |
|---------------------|------------------------------------------------------------------------------|

However, now I want to create a union of Fruit and Vegetable counts. My query now looks like this

(SELECT count(id) as Counter, [Name] FROM Fruits group by name
UNION
SELECT count(id) as Counter, [Name] FROM Vegetables group by name)
FOR JSON PATH

|---------------------|------------------------------------------------------------------------------|
|                           JSON_F52E2B61-18A1-11d1-B105-00805F49916B                                |
|---------------------|------------------------------------------------------------------------------|
|     [{"Counter":2, "Name":"Apple"},{"Counter":3, "Name":"Orange"},{"Counter":7, "Name":"Tomato"}]  |
|---------------------|------------------------------------------------------------------------------|

However, I want it in the format as before, where I have a Type and Value columns (Format A).

I tried doing the following:

SELECT 'FruitsCount' AS "Type", ((SELECT count(id) as Counter, [Name] FROM Fruits group by name
UNION
SELECT count(id) as Counter, [Name] FROM Vegetables group by name) FOR JSON PATH) as "Value"

However, I am presented with Error 156: Incorrect syntax near the keyword 'FOR'.

Then I tried the following:

SELECT 'FruitsAndVegCount' AS "Type", (SELECT count(id) as Counter, [Name] FROM Fruits group by name
UNION
SELECT count(id) as Counter, [Name] FROM Vegetables group by name FOR JSON PATH) as "Value"

However, I am presented with Error 1086: The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator.

I'm stuck in trying to get my "union-ized" query to be in Format A.

Update 1: Here is the desired output

|---------------------|------------------------------------------------------------------------------------------------|
|        Type         |                                   Value                                                        |
|---------------------|------------------------------------------------------------------------------------------------|
|  FruitAndVegCount   | [{"Counter":2, "Name":"Apple"},{"Counter":3, "Name":"Orange"},{"Counter":7, "Name":"Tomato"}]  |
|---------------------|------------------------------------------------------------------------------------------------|

The goal is to only have a single row, with 2 columns (Type, Value) where Type is whatever I specify (i.e. FruitAndVegCount) and Value is a JSON of the ResultSet that is created by the union query.

Upvotes: 0

Views: 235

Answers (2)

Zhorov
Zhorov

Reputation: 29943

If I understand the question correctly, the following statement is an option:

SELECT 
   [Type] = 'FruitAndVegCount',
   [Value] = (
      SELECT Counter, Name
      FROM (
         SELECT count(id) as Counter, [Name] FROM Fruits group by name
         UNION ALL
         SELECT count(id) as Counter, [Name] FROM Vegetables group by name
      ) t
      FOR JSON PATH
   )

Upvotes: 2

SteveC
SteveC

Reputation: 6015

You could do it with two columns, Type and Value, as follows. Something like this

select 'FruitAndVegCount' as [Type],
        (select [Counter], [Name] 
         from (select count(id) as Counter, [Name] from #Fruits group by [name] 
               union all
               select count(id) as Counter, [Name] from #Vegetables group by [name]) u
          for json path) [Value];

Output

Type                Value
FruitAndVegCount    [{"Counter":2,"Name":"apple"},{"Counter":1,"Name":"pear"},{"Counter":2,"Name":"carrot"},{"Counter":1,"Name":"kale"},{"Counter":2,"Name":"lettuce"}]

Upvotes: 2

Related Questions