Drizin / Drizin/InterpolatedSql
Formatting issue for JSON brackets in Postgres jsonb
- 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.