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 17h
Merged PRs (30d)
8

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

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.