Mirrored cosmos db deeply nested json is getting truncated in a sql endpoint query

Hi PBILover, 

When you mirror Azure Cosmos DB into Fabric and query it via the SQL analytics endpoint, any deeply nested JSON is surfaced as a single text column. Today that endpoint typically exposes long strings as VARCHAR(8000), so large JSON blobs (often those “more complex” nested docs) get truncated or fail JSON parsing, which looks like “some documents aren’t returned” or come back cut off. This is expected behavior right now and tied to current limits in preview. Troubleshoot Fabric mirrored databases Data types in Fabric Data Warehouse Mirroring Azure Cosmos DB (Preview). 

Troubleshooting steps:

1. Confirm it’s an endpoint limit (not your data). Check one of the “missing” docs in OneLake or via a notebook; if it’s intact there but cut in the SQL endpoint, you’re hitting the 8 KB-ish limit for JSON text in that endpoint. Microsoft documents truncation of larger JSON in this scenario. Limitations in Cosmos DB in Microsoft Fabric (preview). 
2. Work around the limit for full-fidelity JSON:
   • Warehouse path: Copy the mirrored data into a Warehouse table and store the JSON as VARCHAR(MAX) (currently up to ~1 MB). Then use T-SQL JSON functions against that warehouse table. Troubleshoot Fabric mirrored databases. 
   • Spark path: Create a Lakehouse shortcut to the mirrored data and shred the JSON with Spark (PySpark/SQL) where the strings aren’t capped the same way. 
3. If you must stay on the SQL analytics endpoint:
   • Use OPENJSON with OUTER APPLY to avoid dropping rows that don’t have the array/path you’re expanding. How to: Query nested data in Microsoft Fabric mirrored databases from Azure Cosmos DB (Preview). 
   • Prefer JSON_QUERY() (or OPENJSON) over JSON_VALUE() for long text because JSON_VALUE() returns only NVARCHAR(4000) and will return NULL or error for longer scalars. JSON_VALUE (Transact-SQL). :
   • Note casting to NVARCHAR(MAX) won’t recover data that was already truncated by the endpoint’s VARCHAR(8000) surface type.
4. Seeing inconsistent results? Sometimes it’s just the endpoint’s metadata/scan state-hit Refresh on the SQL analytics endpoint to force a metadata rescan before re-testing.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

Hi PBILover ,

Server or Azure SQL Database, which truncates strings longer than 8000 characters. In the case of deeply nested JSON, the serialized JSON string can easily exceed this limit, leading to truncation of the data you're trying to retrieve.
SQL Server and its variants like Azure SQL use VARCHAR(8000) by default for string columns in queries. If a JSON document exceeds this size, it gets truncated. Since you're querying the mirrored database in Fabric, if the nested JSON is returned as a string field, it might be hitting the truncation limit when queried via SQL, especially when it contains large or deeply nested structures.
Instead of using VARCHAR(8000), which limits the size of your data, you can define the column with VARCHAR(MAX). This allows you to store larger strings without truncation.
If you can’t change the underlying schema but still need to query the JSON data without truncation, consider using the FOR XML PATH method in SQL to handle larger string data, which avoids truncation.
If the JSON is very large and you have strict size constraints in SQL, another option is to store the JSON in a separate blob storage like Azure Blob Storage and store only a reference (e.g., a URI or file path) in the database. This way, you avoid having to handle large strings in the database, and you can load the full JSON data into Power BI from the Blob Storage when needed.
If you're still facing issues, make sure the query timeout is not causing the truncation of results. Complex queries against deeply nested JSON might require more processing time, and Power BI or SQL might terminate the query early.
Increase the query timeout both in SQL Server and Power BI under Advanced settings.

Hope this helps,
Chaithra E.