SQL Server For JSON Path dynamic column name

Question

We are exploring the JSON feature in SQL Sever and for one of the scenarios we want to come up with a SQL which can return a JSON like below

[
  {
    "field": {
      "uuid": "uuid-field-1"
    },
    "value": {
      "uuid": "uuid-value" //value is an object
    }
  },
  {
    "field": {
      "uuid": "uuid-field-2"
    },
    "value": "1". //value is simple integer
  }
  ... more rows
]

The value field can be a simple integer/string or a nested object.

We are able to come up with a table which looks like below:

field.uuid  | value.uuid | value|
------------|----------  | -----|
uuid-field-1| value-uuid | null |
uuid-field-2| null       | 1    |
  ... more rows

But as soon as we apply for json path, it fails saying

Property 'value' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.

Is it possible to do it somehow generate this? The value will either be in the value.uuid or value not both?

Note: We are open to possibility of if we can convert each row to individual JSON and add all of them in an array.

lptr · Accepted Answer · 2020-02-26 13:31:43Z

1

select
    json_query((select v.[field.uuid] as 'uuid' for json path, without_array_wrapper)) as 'field',
    value as 'value',
    json_query((select v.[value.uuid] as 'uuid' where v.[value.uuid] is not null for json path, without_array_wrapper)) as 'value'
from
(
values 
    ('uuid-field-1', 'value-uuid1', null),
    ('uuid-field-2', null,  2),
    ('uuid-field-3', 'value-uuid3', null),
    ('uuid-field-4', null,  4)
) as v([field.uuid], [value.uuid], value)
for json auto;--, without_array_wrapper;

answered Feb 26, 2020 at 13:31

lptr

6,8582 gold badges10 silver badges16 bronze badges

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

2 Comments

Zhorov Over a year ago

Just a note - if value.uuid and value have not null values, the result will be a JSON with duplicate key names.So, as is mentioned in the question, .. The value will either be the value.uuid or value is a really important condition.

Mritunjay Over a year ago

Thanks for the answer @lptr. This seems to be working. Just confirming with you, here the Json Auto is the trick, because if I try same thing with json path it doesn't work.

Zhorov · Accepted Answer · 2020-03-14 14:35:00Z

1

The reason for this error is that (as is mentioned in the documentation) ... FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects. In your case value.uuid and value both generate a key with name value.

I can suggest an approach (probably not the best one), which uses JSON_MODIFY() to generate the expected JSON from an empty JSON array:

Table:

CREATE TABLE Data (
   [field.uuid] varchar(100),
   [value.uuid] varchar(100), 
   [value] int
)
INSERT INTO Data 
   ([field.uuid], [value.uuid], [value])
VALUES   
   ('uuid-field-1', 'value-uuid', NULL),
   ('uuid-field-2', NULL,         1),
   ('uuid-field-3', NULL,         3),
   ('uuid-field-4', NULL,         4)

Statement:

DECLARE @json nvarchar(max) = N'[]'
SELECT @json = JSON_MODIFY(
   @json, 
   'append $', 
   JSON_QUERY(
      CASE
         WHEN [value.uuid] IS NOT NULL THEN (SELECT d.[field.uuid], [value.uuid] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
         WHEN [value] IS NOT NULL THEN (SELECT d.[field.uuid], [value] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
      END   
   )   
)   
FROM Data d   
SELECT @json

Result:

[
    {
        "field":{
            "uuid":"uuid-field-1"
        },
        "value":{
            "uuid":"value-uuid"
        }
    },
    {
        "field":{
            "uuid":"uuid-field-2"
        },
        "value":1
    },
    {
        "field":{
            "uuid":"uuid-field-3"
        },
        "value":3
    },
    {
        "field":{
            "uuid":"uuid-field-4"
        },
        "value":4
    }
]

edited Mar 14, 2020 at 14:35

answered Feb 26, 2020 at 11:53

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

4 Comments

Mritunjay Over a year ago

Thanks for the answer. I did a quick try of your answer and it is working. However I am still new to sql-server JSON features and will try to understand the query provided by you.

Mritunjay Over a year ago

BTW just curious. Why would you think it is not the best way, is there a performance this would cause or something else?

Zhorov Over a year ago

@Mritunjay Not the best one mainly because the statement seems complicated.

Zhorov Over a year ago

@Mritunjay The answer is updated with simplified statement. The approach is the same.

Collectives™ on Stack Overflow

SQL Server For JSON Path dynamic column name

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related