On Tuesday, March 27, 2018 at 2:48:32 PM UTC-7, Jason Landry wrote:
>
> 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.
>
Sequel's DSL supports almost everything you need, except column
definitions in place of column aliases. You'll have to use Sequel.lit for
those:
DB.from(:people, Sequel.pg_jsonb_op(Sequel[:people][:details])[%w'family
pets'].to_recordset.as(:pets, [Sequel.lit('id int'), Sequel.lit('name
text')])).
select_all(:pets).
select_append{[people[:id].as(:parent_id),
people[:name].as(:parent_name)]}.
with(:people, DB.select(Sequel[1].as(:id), Sequel['Jason'].as(:name),
Sequel['{"family": {"pets": [{"id": 101, "name": "Chloe"}, {"id": 102,
"name": "Cooper"}]}}'].cast(:jsonb).as(:details)))
WITH "people" AS (SELECT 1 AS "id", 'Jason' AS "name", CAST('{"family":
{"pets": [{"id": 101, "name": "Chloe"}, {"id": 102, "name": "Cooper"}]}}'
AS jsonb) AS "details") SELECT "pets".*, "people"."id" AS "parent_id",
"people"."name" AS "parent_name" FROM "people",
jsonb_to_recordset(("people"."details" #> ('family', 'pets'))) AS "pets"(id
int, name text)
Thanks,
Jeremy
--
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.
