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
I am not sure I can intuit from your snippet so can you please tell us what exactly do you want to happen?
@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
.
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]
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
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 :-)]
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