On Sat, 4 Mar 2023, Alban Hertroys wrote:
>> But isn’t that the same as with a regular LEFT JOIN?
>
>Similar, but not the same, I’d say.
>
>I do now notice that I made some copying errors there, I was a bit
>nauseous at that time.
np, I’m under pollen attack currently so also not at my best.
>Th
> On 3 Mar 2023, at 20:32, Thorsten Glaser wrote:
>
> On Fri, 3 Mar 2023, Alban Hertroys wrote:
>
>> You can rewrite that into something like this:
>>
>> select jsonb_build_object('opening_times’,
>> obj
>> ORDER BY
>> obj->>'weekday’,
>> obj->>'from_hour’,
>>
On Fri, 3 Mar 2023, Alban Hertroys wrote:
>You can rewrite that into something like this:
>
>select jsonb_build_object('opening_times’,
> obj
> ORDER BY
> obj->>'weekday’,
> obj->>'from_hour’,
> obj->>'to_hour')
>)
>from cot
>cross join lateral jsonb_agg(j
> On 3 Mar 2023, at 0:02, Thorsten Glaser wrote:
>
> On Tue, 28 Feb 2023, Alban Hertroys wrote:
>
>> Perhaps you can use a lateral cross join to get the result of
>> jsonb_build_object as a jsonb value to pass around?
>
> I don’t see how. (But then I’ve not yet worked with lateral JOINs.)
Y
On Tue, 28 Feb 2023, Alban Hertroys wrote:
>Perhaps you can use a lateral cross join to get the result of
>jsonb_build_object as a jsonb value to pass around?
I don’t see how. (But then I’ve not yet worked with lateral JOINs.)
But I c̲a̲n̲ just generate the objects first, I t̲h̲i̲n̲k̲, given one
> On 28 Feb 2023, at 3:54, Thorsten Glaser wrote:
(…)
>> Well, that may be what you want, but it's not what you wrote in
>> the query. Follow David's advice and do
> […]
>> I'm pretty sure that this will only incur one evaluation of the
>> common subexpression, so even though it's tedious to
On Mon, 27 Feb 2023, David G. Johnston wrote:
>Consider this then as a jumping point to a more precise query form:
[…]
>the basic concept holds - produce single rows in subqueries then join those
>various single rows together to produce your desired json output.
Ouch. I’ll have to read up and exp
On Mon, Feb 27, 2023 at 6:22 PM Thorsten Glaser wrote:
>
> Not about the DISTINCTs. I haven’t used JOIN much (usually WHERE
> as inner join) nor on tables this massive, and this is my second
> foray into aggregate functions only.
>
>
Fair.
Consider this then as a jumping point to a more precise
Thorsten Glaser writes:
> On Mon, 27 Feb 2023, Tom Lane wrote:
>> Well, yeah. Simplify it to
>> SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable;
> That’s… a bit too simple for this case.
Sure, I was just trying to explain the rule.
>> For the specific example you give, it's true that any
On Mon, 27 Feb 2023, Tom Lane wrote:
>Well, yeah. Simplify it to
>
> SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable;
That’s… a bit too simple for this case.
>If there are several rows containing the same value of x and different
>values of y, which y value are we supposed to sort the uni
On Mon, Feb 27, 2023 at 5:22 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> Lastly, if you do need to care about normalizing the output of JSON you
> should consider writing a function that takes arbitrary json input and
> reformats it, rather than trying to build up json from scrat
On Mon, Feb 27, 2023 at 4:11 PM mirabilos wrote:
>
> jsonb_build_object('opening_times',
> jsonb_agg(DISTINCT jsonb_build_object(
> 'weekday', cot.weekday,
> 'from_hour', cot.from_hour,
> 'to_hour', cot.to_hour)
> ORDER BY cot.weekday, cot.from_hour, cot.to_hour)
>
mirabilos writes:
> This works well. However, what I seem to be not allowed to do is
> (without the extra COALESCE, to simplify):
> ...
> This is because, when I use DISTINCT (but only then‽), the ORDER BY
> arguments must be… arguments to the function, or something.
Well, yeah. Simplify it to
Hi,
I’ve got a… rather large query (see below), in which I join a complex
data structure (whose exact contents do not, at this point, matter)
together to get some auxiliary data to expose as JSON field.
In this query I can use, for example…
jsonb_build_object('user_permissions',
jsonb_agg(DI
14 matches
Mail list logo
