Raw SQL Query with json array passed as a bind variable

Questions / Help
,
1

Hi could you help me with this problem? I’m trying to run a raw query using Ecto.Adapters.SQL.query with json array passed as a bind variable. I tested simplified version without passing anything as below and it works fine:

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from json_to_recordset('[{
        "id": 1,
        "time": 6
      },
      {
        "id": 2,
        "time": 6
      }]') as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      []
    )

Now I would like to pass a json array, so I tried:

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from json_to_recordset($1) as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      ["[{\"id\":7,\"time\":11}, {\"id\":1,\"time\":10}]"]
    )

but it fails with QUERY ERROR…

basically I would like to be able to pass a list of structs encoded to json array like below:

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from json_to_recordset($1) as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      [Jason.encode!(list_of_structs)]
    )

but of course it doesn’t work either. Would you have any hint how to construct proper json array and pass it to the above raw query?

2

I have a vague memory of trying to do the same thing but I only found some comments without much context on my notes

fragment("?.key_values->'keys' @> any (array[?]::jsonb[])", c, ^array), select: c.id

Its not a raw query but a fragment but maybe the array[?]::jsonb[] could help you.
I have not tested it, not sure if it will work because it looks like I was using a simple array of numbers and not structs

3

Hi @cpgo, thanks for the hint but unfortunately it didn;t work:

[debug] QUERY ERROR db=0.0ms queue=3.7ms idle=1619.2ms
update data.entries as e
set time = n.time
from jsonb_to_recordset(ARRAY[$1]::jsonb[]) as n("id" bigint, "time" int)
where e.id = n.id;
 ["[{\"id\":14,\"time\":\"20\"}]"]

I guess it boils down to preparing the string that represents json array in a way that quotes are properly interpreted. Unfortunately it is not as simple as using the output of Jason.encode!(list_of_structs). It needs more parsing. BTW Is there any way to check why this query is failing? Are there any logs levels that could be switched on (maybe in postgrex configuration) that would give more info about the query error?

4

Did you try not manually encoding the params, but simply providing a map of data?

5

Hi @LostKobrakai,

I’m not sure that I understand you correctly. Basically I tried to pass the output of Jason.encode!(update_list) as below. Update_list is a list of maps with proper keys. Do you mean to bypass Jason output and pass a list of those maps directly? If yes, I didn’t try it yet.

    list = Jason.encode!(update_list) |> IO.inspect()

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from jsonb_to_recordset(ARRAY[$1]::jsonb[]) as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      [list]
    )

IO.inspect output:

"[{\"id\":14,\"time\":\"1\"}]"
6

That’s what I meant.

7

Still no luck :slight_smile:

 list = [%{id: 14, time: 20}, %{id: 1, time: 2}]

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from jsonb_to_recordset($1::jsonb[]) as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      [list]
    )

Logs:

[debug] QUERY ERROR db=0.0ms queue=2.8ms idle=1293.3ms
update data.entries as e
set time = n.time
from jsonb_to_recordset($1::jsonb[]) as n("id" bigint, "time" int)
where e.id = n.id;
 [[%{id: 14, time: 20}, %{id: 1, time: 2}]]
8

The type should be ::jsonb not ::jsonb[]. The latter is an (native) array of jsonb values.

1 Like
9

It works now after changing to ::jsonb :smiley: Thank you @LostKobrakai ! So SQL.query handles parsing well on its own, we don’t need Jason for this.

    Ecto.Adapters.SQL.query(
      Flip.Repo,
      """
      update data.entries as e
      set time = n.time
      from jsonb_to_recordset($1::jsonb) as n("id" bigint, "time" int)
      where e.id = n.id;
      """,
      [list]
    )

Logs:

[debug] QUERY OK db=6.5ms queue=0.3ms idle=1042.8ms
update data.entries as e
set time = n.time
from jsonb_to_recordset($1::jsonb) as n("id" bigint, "time" int)
where e.id = n.id;
 [[%{id: 13, time: #Decimal<10>}]]
3 Likes