Forum Discussion

kamiljalil's avatar
kamiljalil
Copper Contributor
Nov 02, 2023

Fetching JSON object from Cosmos DB in Azure Synapse Studio fails

Hi everyone!

Facing an issue while fetching document from linked Cosmos DB in Azure Synapse Studio
I've tried:
  • fetching this object as:
    •   VARCHAR(MAX)
    •   NVARCHAR(MAX)
  • identifying full path in WITH clause:
    •   failing_complex_object VARCHAR(MAX) '$.failing_complex_object'
  • trying to decompose it in SELECT clause:
    •   ,JSON_QUERY([target_table].[failing_complex_object])
    •   ,JSON_VALUE(failing_complex_object, '$.failing_complex_object')

It always returns NULL. 

  • Complex objects without issue are bigger size and one of them is JSON object,
    the other one is JSON array.
  • Complex object with issue is a valid JSON object.
The question is:
WHY two complex objects are fetching fine but this one fails.

 

Data examples are below.

 

Any help would be greatly appreciated.

 

P.S.

Due to NDA cannot share with you the actual DB data but I assure you that SCHEME is absolutely the same.

 

P.S. P.S.

Sorry for JSON and SQL examples. Couldn't load all the examples using "Insert code sample" tool. It didn't work.

 

fetch example

Document example
{
  "field_01": "title",
  "field_02": "12345678",
  "complex_object_01": [
    {
      "field_01": 1,
      "field_02": "data"
    },
    {
      "field_01": 2,
      "field_02": "data"
    }
  ],
  "complex_object_02": {
    "rows": [
      {
        "field_01": 0.1,
        "field_02": 0.0,
        "field_03": 0.1,
        "rowIndex": 0
      }
    ]
  },
  "failing_complex_object": {
    "data_01": {
      "field_01": 0,
      "field_02": 0
    },
    "data_02": {
      "field_01": 0,
      "field_02": 0
    }
  }
}
 
script example
SELECT
    field_01,
    field_02,
    complex_object_01,
    complex_object_02,
    failing_complex_object
FROM OPENROWSET (
    PROVIDER = 'CosmosDB',
    CONNECTION = '',
    OBJECT = 'target_table')
    WITH (
        field_01 VARCHAR(MAX),
        field_02 VARCHAR(MAX),
        complex_object_01 VARCHAR(MAX),
        complex_object_02 VARCHAR(MAX),
        failing_complex_object VARCHAR(MAX)
        )
AS [target_table]
WHERE field_01 = 'title' AND field_02 = '12345678'
azure cosmos db
Azure Synapse Analtyics
Azure Synapse Link
Transact SQ

1 Reply

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Nov 15, 2025

    Would suspect that the reason your failing_complex_object always returns NULL is that Synapse OPENROWSET with Cosmos DB doesn’t automatically flatten nested JSON objects into string columns. Arrays and simple objects serialize fine, but nested objects with multiple child properties (like your data_01 and data_02) often fail unless you explicitly cast them as NVARCHAR(MAX) and then use JSON_QUERY without a path. In other words, the column definition in your WITH clause is discarding the nested object because Synapse expects a scalar value, not a structured JSON object.

Resources