How to handle null values with JSON function in SQL statements: Function json(unknown) does not exist
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
Is it possible to avoid issues with null values from dynamic parameters when using the JSON function in SQL queries? The problem is that if a null value is given to a query (See example below as a rough, untested example), an error occurs Npgsql.PostgresException: "42883: Function json(unknown) does not exist
The following code is an example. The parameter areAttributesNull and the string replace is added to avoid the issue, but this is not really a desired (and practicable) solution. Maybe, there is already a more simple way to handle the data properly without the need to string-replace SQL statements, but I didn't find any other solution yet...
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
public class Program
{
public async Task<int> UpdateNodes(List<Node> nodes, bool areAttributesNull = false)
{
if (nodes is null || nodes.Count == 0)
return 0;
// Not relevant, get a connection to the database somehow.
await using var connection = await this.GetDatabaseConnection();
var sqlParams = nodes.Select(node => new DynamicParameters(new
{
id = node.Id,
description = node.Description,
jsonattributes = node.JsonAttributes
}));
var sql = @"UPDATE node SET (id, description, jsonattributes) =
(@id, @description, JSON(@jsonattributes)) WHERE id = @id;";
if (areAttributesNull)
{
sql = sql.Replace(", jsonattributes", "");
sql = sql.Replace(", JSON(@jsonattributes)", "");
}
return await connection.ExecuteAsync(sql, sqlParams);
}
public async Task<NpgsqlConnection> GetDatabaseConnection()
{
var connection = new NpgsqlConnection("SomeConnectionString");
await connection.NpgsqlConnection.OpenAsync();
return connection;
}
}
public class Node
{
public string Id { get; set; }
public string Description { get; set; }
public string? JsonAttributes { get; set; }
}
EDIT: For reference, this is no database issue. E.g. SELECT JSON(null), * FROM node; would work properly. Must be something with the mapping.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No repository file or test is named. Start by reproducing the C# UpdateNodes example with a null jsonattributes parameter and the SQL JSON(@jsonattributes) expression, then trace how Dapper maps that parameter. Done means the null case has defined behavior without string replacement and is covered by a regression test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, postgresql, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100