Using JSON path in tabular input data for JSON output schema in an Azure Data Factory flow

Question

I've seen how to use the Aggregate transformation in an ADF Data Flow along with a static hierarchy specified in a Derived Columns transformation and the collect() function to output custom JSON.

What I want to do is a little more dynamic. If my tabular data contains one column with a single attribute value, and another column with a string representing the JSON path to which I want the first column's attribute value to be output in the JSON schema, how would this sort of transformation be achieved?

Table Input:

CREATE TABLE EAV (EntityID int, AttributePath nvarchar(500), AttributeValue nvarchar(max))

INSERT EAV VALUES
(1, 'user.firstName','John'),
(1, 'user.lastName','Doe'),
(1, 'user.address.city','Pittsburgh'),
(1, 'user.address.state','Pennsylvania'),
(2, 'user.firstName','Jane'),
(2, 'user.lastName','Doe'),
(2, 'user.address.city','Pittsburgh'),
(2, 'user.address.state','Pennsylvania');

JSON Output:

{
    "user": {
        "id" : 1
        "firstName": "John",
        "lastName": "Doe",
        "address": {
            "city": "Pittsburgh",
            "state": "Pennsylvania"
        }
    },
    "user": {
        "id" : 2
        "firstName": "Jane",
        "lastName": "Doe",
        "address": {
            "city": "Pittsburgh",
            "state": "Pennsylvania"
        }
    },
}

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Oct 20, 2021 at 23:58

Charlieface · Accepted Answer · 2021-10-21 12:14:38Z

1

SQL Server doesn't deal well with dynamic JSON keys. You can use dynamic SQL for this

Basically, we take the distinct path expression, pivot them out, and select them using FOR JSON PATH

DECLARE @cols nvarchar(max) = (
    SELECT STRING_AGG(QUOTENAME(AttributePath),',')
    FROM (SELECT DISTINCT AttributePath FROM EAV) EAV
);

DECLARE @sql nvarchar(max) = N'
SELECT
    id = EntityID,
    ' + @cols + '
FROM EAV
PIVOT (
    MAX(AttributeValue) FOR AttributePath IN (
        ' + @cols + '
    )
) pvt
FOR JSON PATH;
';

EXEC sp_executesql @sql;

Result

[
   {
      "id":1,
      "user":{
         "address":{
            "city":"Pittsburgh",
            "state":"Pennsylvania"
         },
         "firstName":"John",
         "lastName":"Doe"
      }
   },
   {
      "id":2,
      "user":{
         "address":{
            "city":"Pittsburgh",
            "state":"Pennsylvania"
         },
         "firstName":"Jane",
         "lastName":"Doe"
      }
   }
]

db<>fiddle

edited Oct 21, 2021 at 12:14

answered Oct 21, 2021 at 12:07

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

4 Comments

coryseaman Over a year ago

This works great for my example! Unfortunately I oversimplified it and the real-life data includes, for example, array attributes whose multiple values are multiple rows in SQL (i.e. if person 1 had two firstName values, John and Jim, in separate rows with the same AttributePath). From my testing, the resulting JSON never gets a firstName collection object with multiple values... rather, it either only ends up with one of them, or it ends up creating multiple person 1 objects, each with subsets of attributes. Is there any way to work around that?

Charlieface Over a year ago

Not really, because duplicate keys are not to JSON specification. Most parsers will just pick the first one they come across. You could perhaps number them FirstName1 FirstName2 using ROW_NUMBER however that would be fairly complex and a new Stack Overflow question is warranted

coryseaman Over a year ago

I tried a fiddle that utilized STRING_AGG() in the PIVOT, but I'm getting a syntax error... would something like that work? PIVOT ( STRING_AGG(AttributeValue, '','') FOR AttributePath IN ( ' + @cols + ' ) ) pvt FOR JSON PATH; The end goal being to turn multiple values in to arrays: ['Jim','Joe']

Charlieface Over a year ago

You are not allowed to use PIVOT with any expression, it has to be of the form AggregateFunction(ActualColumnName). You could pre-aggregate it like this dbfiddle.uk/…

Collectives™ on Stack Overflow

Using JSON path in tabular input data for JSON output schema in an Azure Data Factory flow

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related