npgsql (postgresql c#) insert array of json objects-CodePudding

I would like to insert data as array of json objects into a postgresql table column.

db table structure (create command for the table):

CREATE TABLE Recipes (
key SERIAL PRIMARY KEY,
name varchar(255) NOT NULL,    
ingredients json[],
duration int);

A working sql query example (with any sql client):

INSERT INTO
recipes (name, duration, ingredients)
VALUES(
'title',
60,
array['{"name": "in1",
"amount": 125,
"unit": "g" }',
'{ "name": "in2",
"amount": 75,
"unit": "ml" }'
]::json[]);

In npgsql I try it as following:

 //Create connection
        var connString = $"Host={host};Port={port};Username={user};Password={password};Database={database}";
        Npgsql.NpgsqlConnection.GlobalTypeMapper.UseJsonNet();
 

        await using var conn = new NpgsqlConnection(connString);
        await conn.OpenAsync();
 //create query command
     await using var cmd = new NpgsqlCommand("INSERT INTO recipes (name,duration,ingredients) VALUES (@p0,@p1,@p2)", conn)
            {

                Parameters =
                    {                                                       
                        new NpgsqlParameter("p0", recipe.Name),                            
                        new NpgsqlParameter("p1", recipe.Duration),                            
                        new NpgsqlParameter("p2", recipe.Ingredients)
                    }
            };
 //execute query
    await cmd.ExecuteNonQueryAsync();

I have two classes from which I want generate the query params:

public class Recipe
{
    public Recipe() { }
    public string Name { get; set; }
    public int Duration { get; set; }

    //tried this --> did not help
    //[Column(TypeName = "jsonb")]
    public Ingredients[] Ingredients { get; set; }

}

public class Ingredients
{
    public string Name { get; set; }
    public float Amount { get; set; }
    public string Unit { get; set; }
}

Since I was not able to do so, I tried to debug with hard coded stuff like this:

            JObject jsonObject1 = new JObject();
            jsonObject1.Add("name", "value1");
            jsonObject1.Add("amount", 1);
            jsonObject1.Add("unit", "ml");

            JObject jsonObject2 = new JObject();
            jsonObject2.Add("name", "value2");
            jsonObject2.Add("amount", 2);
            jsonObject2.Add("unit", "g");

            JObject jsonObject = new JObject();
            jsonObject.Add("name", "value0");
            jsonObject.Add("amount", 19);
            jsonObject.Add("unit", "ts");

            //OPTION 1 to insert into the query command instead of recipe.Ingredients
            JArray ingredientsJArray = new JArray();
            ingredientsJArray.Add(jsonObject1);
            ingredientsJArray.Add(jsonObject2);
            ingredientsJArray.Add(jsonObject);

            //AND OPTION 2 to insert into the query command instead of recipe.Ingredients
            JObject[] ingredientsArray = new JObject[3];
            ingredientsArray[0] = jsonObject;
            ingredientsArray[1] = jsonObject1;
            ingredientsArray[2] = jsonObject2;

For the Json handling I use Newtonsoft.Json (Nuget Package)

I also tried to make an array of (json formatted) strings to get the query working which understandably lead to exceptions which say that I use "text[]" instead of "json[]".

Is it really that hard to achieve this with c# npgsql? In other languages like js (npm package of pg) it is super easy. Or am I missing s.th. very obvious?

Remark: without the json[] column the query works like a charm.

Some help is highly appreciated. Thx!

CodePudding user response：

PostgreSQL has an array type which is very different from a JSON array; in other words, you can't write a .NET JArray (which maps to PG json) to json[], which is your column. Instead, map a regular .NET array of JObject as follows:

var jsonObject1 = new JObject
{
    { "name", "value1" },
    { "amount", 1 },
    { "unit", "ml" }
};

var jsonObject2 = new JObject
{
    { "name", "value2" },
    { "amount", 2 },
    { "unit", "g" }
};

cmd.CommandText = "INSERT INTO Recipes (ingredients) VALUES (@p)";
cmd.Parameters.Add(new("p", NpgsqlDbType.Json | NpgsqlDbType.Array) { Value = new[] { jsonObject1, jsonObject2 } });
await cmd.ExecuteNonQueryAsync();

Note that you need to manually specify the NpgsqlDbType as in the above, opened this issue to make this better.

Finally, Npgsql also supports the built-in System.Text.Json as an alternative to Newtonsoft.Json - no additional plugin is needed for that.