Ecto dynamic queries with jsonb_each_text in PG

Questions / Help
, ,
1

Hi all,

I was trying to use dynamic queries and fragment but I’m struggling with this query:

select json_data.key , Sum(json_data.value::INTEGER) as value
from my_store, jsonb_each_text(my_jsonb_column) as json_data
group by json_data.key

I’m using PostgreSQL and jsonb_each_text to extract a record hey-value.

How can I use/build a dynamic query based on that?

Thanks

2

You cannot use functions in from part in Ecto. You need to fall back to JOINs:

from store in "my_store",
  inner_lateral_join: json_data in fragment("jsonb_each_text(?)", store.my_jsonb_column),
  group_by: json_data.key,
  select: %{
    key: json_data.key,
    value: sum(json_data.value)
  }
2 Likes