I am trying to figure out how to use Sequel to extract data in jsonb into
set-oriented data. I have a contrived example that illustrates it in sql:
with people as (
select 1 as id, 'Jason' as name, '{"family": {"pets": [{"id": 101,
"name": "Chloe"}, {"id": 102, "name": "Cooper"}]}}'::jsonb as details
)
select people.id as parent_id, people.name as parent_name, pets.*
from people,
jsonb_to_recordset(people.details#>'{family, pets}') as pets(id int, name
text)
The output of this query is
┌───────────┬─────────────┬─────┬────────┐
│ parent_id │ parent_name │ id │ name │
╞═══════════╪═════════════╪═════╪════════╡
│ 1 │ Jason │ 101 │ Chloe │
│ 1 │ Jason │ 102 │ Cooper │
└───────────┴─────────────┴─────┴────────┘
After loading the pg_json and pg_json_ops, I can do this:
details = Sequel.pg_jsonb_op(:details)
pets = Sequel.pg_jsonb_ops(details.extract %w(family pets))
But then I'm lost. I can't see how to get pets.to_recordset into the query
like it is above. And I can't figure out how to specify alias, columns,
and datatypes.
Any help would be appreciated!
Jason
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
