Fetching JSON object from Cosmos DB in Azure Synapse Studio fails

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.