Hacker News Reader

Comment by erikpukinskis | Hacker News Reader

> You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

For anyone (like me) who is not quite able to visualize this, here is an example:

    SELECT json_agg(trips)
    FROM (
        SELECT 
            json_agg(
                json_build_object(
                    'recorded_at', created_at, 
                    'latitude', latitude, 
                    'longitude', longitude
                )
            ) as trips
        FROM data_tracks
        GROUP by trip_log_id
    )s

From StackOverflow user S-man https://stackoverflow.com/a/53087015

View on HN

As someone who would like to use SQL to return a tree-like structure, how well does this scale (in terms of readability, performance, etc.) when the query is extremely large and nested? For example, if we are attempting to replace GraphQL with some sort of SQL.

I'm super unfamiliar with this space, and would love to know whether it is a feasible/worthy goal to replace GraphQL queries with generated SQL.

Very well, I run huge nested CTEs with json aggregation in SQLite for findsight.ai and there’s really not any noticeable performance overhead.

A good editor (DataGrip) helps.

Like with most performance questions, it depends.

I see someone else said that it scaled well for sqlite. For postgres, it very much depends. Building a json object is much, much slower than selecting a row. Updating JSON objects involves building a new one, since you need to replace the entire object, so avoid building your schema in a way that requires updating JSON objects.

(Experience is with postgres 14. To be fair, performance was generally fine up until the 100s of millions of rows)

JSON query performance in postgres though is generally quite good. If you have static data, throwing it into a JSONB column with a jsonb_path_ops GIN index (or the default if you need the extra query flexibility) scales well up to billions of rows.

building a json object is slow compared to returning rows. but its far faster than multiple trips to the database to build a json object in the application layer.

You might be interested in PostGraphile. [1]

[1] https://www.graphile.org/postgraphile/

Reply on news.ycombinator.com