Json.Document() ECMA-262 non-conformance - Power Query throws exception on duplicate JSON key

[EdwardH]

The following Power Query code throws an error that halts processing:

let
    Source = "{""key1"":""val1"",""key2"":""val2"",""key1"",""val3""}",
    Custom1 = Json.Document(Source)
in
    Custom1

The error is "DataFormat.Error: Duplicate name 'key1'."

However, ECMA-262 has this to say about duplicate keys:

In the case where there are duplicate name Strings within an object, lexically preceding values for the same key shall be overwritten.

Power Query throwing an error causes incompatibility with other tools which are spec-compliant.

[EdwardH]

ECMA-404 says:

6 Objects
An object structure is represented as a pair of curly bracket tokens surrounding zero or more name/value pairs. A name is a string. A single colon token follows each name, separating the name from the value. A single comma token separates a value from a following name. The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs. These are all semantic considerations that may be defined by JSON processors or in specifications defining specific uses of JSON for data interchange.

The standard explicitly says that name strings are not required to be unique, and the question of how to handle non-unique name strings is answered in ECMA-262.

Your comment is that the Microsoft Power Query JSON parser implementation "aligns with" the standard. I disagree with that word choice as it implies a reduced level of agency. I recognize the last sentence I quoted allows JSON processors to define restrictions, and you could argue that an implementation is "standard-compliant" with such restrictions. But those are choices made at the implementors discretion, and not required to achieve standards-compliance. Taking the position that one might include restrictions in a JSON parser to the extreme, you could just as easily say that a JSON parser that requires all name strings to begin with the letter Q is "standards-compliant" although obviously it would be awful to use, especially if the target user base is as broad and varied as Power Query's. 

Technically I can see that calling the implementation non-conforming could be debated. However, the core point stands. Microsoft at its own discretion and not as a requirement to comply with a standard has publicy deployed an implementation of a system that, while intended to be used widely for data interexchange, breaks compatibility with other standards-compliant systems which did not impose upon themselves a non-required restriction.

In my opinion, this is a bug, not a feature.

[v-saisrao-msft · Accepted solution]

Hi @EdwardH 
If your query is still unresolved, please consider raising a Microsoft support ticket. Below is the link to raise the ticket:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

[jennratten]

Hello @EdwardH - thank you for posting to the community.  

Power Query uses  an implementation that does not permit duplicate keys, which aligns with ECMA-404 standards.

In order to work-around this limitation you can pre-process the JSON to remove duplicates, or handle them appropriately, before loading the data into Power Query. 

Please let me know if I can further assist with this.