In ecto how to return results that have more than one row or column using fragments

Questions / Help
1

all the code I have seen and written only returns a singe column/row
I am working on json data in postgres and need rows and columns & fragments to accomplish this [I think]

for code like this:

def list_score_for_call_id(acct, call_id \ 11111117) do

query =
  from(c in CallLog,
  where: c.call_id == ^call_id,
  select: %{
        post_call_script_result: fragment(
          "SELECT * FROM jsonb_each(?)", c.post_call_script_result),
         },
    order_by: []

  )

xxxxx.Repo.all(query, prefix: Cogito.Customers.schema_prefix(acct))
end

I get errors like this, or similar:

Postgrex.Error at GET /portal

ERROR 42601 (syntax_error) subquery must return only one column

any help appreciated

2

I am not sure I can intuit from your snippet so can you please tell us what exactly do you want to happen?

3

@jwaldner can you show the kind of SQL you would expect this to do? I’ve only seen sub-selects like this when doing a join.

4

well i guess what I am after is a way to use fragments in an ecto select and be able to return more than a scalar value [like a one row one column select statement]

I am new to ecto, but I have figured out that I can get json functions working in ecto using fragments, but anytime the function would return more than one row or column I get an error.

So is there a way to make a fragment [used in a select ] return rows & columns or should I be doing this some other way?

It might be helpful to show the json I have:
[Scores can have more than one item]
[I need to get the Live Answer Time and its field in one row]
[another score might be Negative Word and it would need its own row ]

post_call_script_result: %{
“scores” => %{
“Live Answer Time” => %{
“comment” => “Lets talk about this in quarterly review”,
“maximum” => 5,
“score” => 0
}
}
},

so what i need is
name comment maximum score
Live Answer Time “Lets talk…” 5 0
Word Score “bla bla” 6 3

I was hoping to pivot the json data but i cannot seem to get the fragment to give me more than the json key “Live Answer Time”: for example.

anything more than one row or column from the jason function in PostgreSQL and I get an error

I wanted to try and use PostgreSQL to manipulate the data rather than post processing with elixir…

Sorry for not being more clear. This stuff is still pretty new to me, [cool though]

5

I think the error it’s returning is because you’re trying to return the fragment as a single column value (post_call_script_result), but the fragment itself evaluates to many columns (…actually 2 columns). The appropriate form to do that would be to join CallLog to the fragment you currently have, but the fragment will need to be modified to include a join key. This is what @benwilson512 might have been referring to about only having seen this in sub-selects with joins.

1 Like
6

not sure I understand how it evaluates to many columns…thats kind of my question though I admit i may not know the terminology well enough yet…
I need more than one column and row and am not sure of the syntax or pattern to get that…all of the data I need is in those json fields…in post_call_script_result…I am obviously

[quote=“imkleats, post:5, topic:52143”]
trying to return the fragment as a single column value
[/quote] [but that is not what I want :-)]

7

At the end of the day, Ecto is just asking Postgres to return results, and postgres always returns results as rows and columns. I would simply return the JSON as one of the values of one of the columns, and then decode it and transform it in Elixir.

2 Likes