Drizin / Drizin/InterpolatedSql

Formatting issue for JSON brackets in Postgres jsonb

Open
#22 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
279
Forks
15
PR merge metrics
No merged PRs in 30d

Description

InterpolatedSql.Dapper Version="2.3.0"
Npgsql Version="9.0.2"

Table:
```
create table if not exists Translations(
id bigserial primary key,
original_lang text,
translations jsonb not null
);

insert into Translations (original_lang, translations) values (
'en',
'{"fr": "wash le plates", "en": "wash the plates"}'::jsonb
)
```

Given the following valid psql query
`select t.translations->>'en' as translation_text from Translations t where t.translations@>'{"fr": "wash le plates"}';`

Returns a single column `translation_text` with the value "wash the plates"

The query when compiled with InterpolatedSql.Dapper looks like
```
var res = await connection.SqlBuilder(
$$"""
select t.translations->>'{{queryToLanguage}}' as translation_text from Translations t where t.translations@>'{"{{queryByLanguage}}": "{{queryByText}}"}';
""")
.QueryFirstAsync();
```

In use with `InterpolatedSqlBuilderOptions.DefaultOptions.AutoFixSingleQuotes = false` to allow for the single quotes.

This gives the following error:
> Unhandled exception. Npgsql.PostgresException (0x80004005): 22P02: invalid input syntax for type json
> POSITION: 93
> DETAIL: Expected string or "}", but found "{".

If we look at the query string generated by the SqlBuilder...

```
"select t.translations->>'@p0' as translation_text from Translations t where t.translations@>'{{@p1: @p2}}';" (@p0='en', @p1='fr', @p2='wash le plates')
```

We can see that the issue is at the jsonb formatting `t.translations@>'{{@p1: @p2}}';`. The raw `{` is printed twice. As is the closing bracket.
My desired value for the query string is
`select t.translations->>'@p0' as translation_text from Translations t where t.translations@>'{"@p1": "@p2"}';`

Jsonb requires the body, therefore I use double formatter `$$`.

If i execute without using the `InterpolatedSql.Dapper.SqlBuilders.SqlBuilder`, using dapper and an unsafe interpolated string query, the following works...

```
var res = await connection.QueryFirstOrDefaultAsync(
$$"""
select t.translations->>'{{queryToLanguage}}' as translation_text from Translations t where t.translations@>'{ "{{queryByLanguage}}": "{{queryByText}}" }';
""");
```

I have only picked this library up today. Perhaps it is my mistake?

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.