Forum Discussion

jonesb321's avatar
jonesb321
Copper Contributor
Jul 02, 2020

Derived json with null property value

I am constructing a json object, and I want all properties to exist, even if the source value was null.  Is this possible, or am I simply going about it incorrectly?

 

Here is my data mapping expression:

@(address1=byName('Address1'),
    address2=byName('Address2'),
    city=byName('City'),
    state=byName('State'),
    zip=byName('Zip'))

 

Here is the output:

{
    "address1": "123 Any Street",
    "city": "Smalltown",
    "state": "WA",
    "zip": "12345"
}

 

Here is the desired output:

{
    "address1": "123 Any Street",
    "address2": null,
    "city": "Smalltown",
    "state": "WA",
    "zip": "12345"
}

1 Reply

  • jonesb321's avatar
    jonesb321
    Copper Contributor

    We get this behavior in the Copy Activity, at least when the source is SQL and the sink is JSON.

     

    Fundamentally, I want something similar to the INCLUDE_NULL_VALUES option for SQL referenced https://docs.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-option?view=sql-server-ver15.

Resources