Thanks, very much appreciate it!
On Tuesday, March 27, 2018 at 6:50:46 PM UTC-4, Jeremy Evans wrote:
>
> 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.
