Azure / Azure/data-api-builder

[Known Issue] Detect JSON columns in Azure SQL

Open
#444 12 comments 0 reactions 0 assignees View on GitHub
2.1 improvement known-issue
Dominant language
C#
Stars
1.5k
Forks
370
Avg merge
3d 22h
Merged PRs (30d)
9

Description

We should be able to automatically detect if a column contains JSON data and avoid escaping it in the output. Right now if I have the following table:

```
create table s003b.todos
(
id int not null constraint pk__s003b_todos primary key default (next value for s003b.globalId),
category_id varchar(3) collate Latin1_General_BIN2 not null constraint fk__s003b_todos__s003b_categories references s003b.categories(id),
title nvarchar(100) not null,
[description] nvarchar(1000) null,
tags nvarchar(max) null check ( isjson(tags)= 1 ),
completed bit not null default(0)
)
```

and I store JSON data in the "tags" column, I get the following output:

```
{ data": {
"todos": {
"items": [
{
"id": 10000,
"title": "item-001",
"completed": false,
"tags": "[{\"tag\":\"red\"}]",
"category": {
"id": "f",
"category": "Family"
}
}
```

where `tags` contains encoded JSON...even if the content is valid JSON itself. Right now Azure SQL DB doesn't have a native JSON data type, but we can check if a column contains JSON data by checking the check constraint that *should* have been created to allow only JSON data to be inserted (see table definition above). This query can return which columns should be treated as JSON:

```sql
select
s.[name] as [schema_name],
t.[name] as [table_name],
c.[name] as [columne_name],
ck.[definition],
case when (ck.[definition] like '%isjson(/[' + trim(c.[name]) + '/])=(1)%' escape '/') then 1 else 0 end as [isjson]
from
sys.check_constraints ck
inner join
sys.tables t on ck.[parent_object_id] = t.[object_id]
inner join
sys.columns c on t.[object_id] = c.[object_id] and ck.parent_column_id = c.[column_id]
inner join
sys.schemas s on t.[schema_id] = s.[schema_id]
```

Contributor guide

Open the contributing guide

Research direction

Start by tracing Azure SQL schema introspection and the output serialization path in the data-api-builder repository, using the issue's check-constraint query as the expected metadata source. Confirm how JSON columns are identified and how their values are emitted; done means valid JSON column contents are not escaped while ordinary text remains unchanged.

Written by the indexing model from the issue text.

Assessment

Tech stack
azure, sql
Domain
api, databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.