Using complex JSON functions

We're using AWS Aurora + knex at the moment, and we've got some knex helpers like this:

/**
 * Takes an object and returns a Postgres json_build_object()
 * @param obj A map of any shape
 * @param alias Column alias
 * @returns json_build_object() SQL code
 */
export function jsonBuildObjectSql(
  obj: Record<string, unknown>,
  alias?: string,
  checkFieldForNull?: string
): string {
  return (
    (checkFieldForNull
      ? `case when ${checkFieldForNull} is not null then `
      : '') +
    'json_build_object(' +
    Object.entries(obj)
      .map(pair => [`'${pair[0]}'`, pair[1]])
      .join(', ') +
    ')' +
    (checkFieldForNull ? ' else null end' : '') +
    (alias ? ` as ${alias}` : '')
  );
}

/**
 * Takes an object and returns a Postgres json_build_object()
 * @param obj A map of any shape
 * @param alias Column alias
 * @returns json_build_object() SQL code
 */
export function jsonBuildObjectSql(
  obj: Record<string, unknown>,
  alias?: string,
  checkFieldForNull?: string
): string {
  return (
    (checkFieldForNull
      ? `case when ${checkFieldForNull} is not null then `
      : '') +
    'json_build_object(' +
    Object.entries(obj)
      .map(pair => [`'${pair[0]}'`, pair[1]])
      .join(', ') +
    ')' +
    (checkFieldForNull ? ' else null end' : '') +
    (alias ? ` as ${alias}` : '')
  );
}

I'd love to move to Supabase, but I'm not sure if the engine is capable of this or not (strictly speaking of @supabase/supabase-js). I'd rather not use actual DB connections, as all of our back-end is in Lambda functions, so the API approach is much neater

32 Replies

Sam HulickOP•2y ago

Supabase's support AI already lied to me and said there was a supabase.sql() function 😆 if only!

Socal•2y ago

as in you're looking to replace knex with the supabase-js client?

Sam HulickOP•2y ago

well, just move from AWS Aurora to Supabase. knex isn't really important but we definitely need to be able to use more complex SQL like case statements, JSON funcs, etc

Socal•2y ago

you're not going to be able to use the supabase-js client to run raw sql queries like you can in knex. you can use knex with supabase.

Sam HulickOP•2y ago

how? or it's possible I don't need these convoluted queries w/ Supabase

Socal•2y ago

if you're using the postgres compatible version of aurora then supabase will be similar. it's really where you want to store your logic for this stuff. you can probably just create database views instead of using knex to build queries if your trying to place some kind of business logic on the data

Sam HulickOP•2y ago

like, this is kind of amazing:

  const { data, error } = await supabase.from("users").select(`
    email,
    first_name,
    user_settings ( username )
  `);

  const { data, error } = await supabase.from("users").select(`
    email,
    first_name,
    user_settings ( username )
  `);

super easy way to do JOINs

garyaustin•2y ago

Supabase gives you full access to the database (less a few superuser features) with direct database ports. So from secure environments you can run SQL. If the REST API's can't do something you need then you also have the ability to use rpc calls to Postgres functions or views as Socal mentions.

Sam HulickOP•2y ago

bear in mind, I'm brand new to Supabase.. so, still going through the docs

garyaustin•2y ago

The detail reference of what you can do with the REST API is here: https://postgrest.org/en/stable/references/api.html It shows the raw URL formats, but the clients all use this.

PostgREST

API

PostgREST exposes three database objects of a schema as resources: tables, views and stored procedures. Tables and Views, Stored Procedures, Schemas, Computed Fields, Domain Representations, Pagination and Count, Resource Embedding, Resource Representation, Media Type Handlers, Aggregate Function...

Sam HulickOP•2y ago

gotcha. so for more advanced stuff like using JSON path queries in Postgres, you'd have to make a Postgres function

garyaustin•2y ago

The biggest things you can't do thru rest are complex array and json operations (but they can do alot including filter on them) and transactions. You have to use an rpc call if you need to operations like read and update to run as a transaction.

Sam HulickOP•2y ago

so, so search for an object in an array (jsonb) based on a unique ID, you'd need to use RPC

garyaustin•2y ago

You should be able to do that with the REST API, but sometimes figuring out the syntax is tough. I usually bail if I don't figure it out quickly and just use rpc, but then I know sql reasonably well. There is a specific section in the docs I linked on jsonb.

Sam HulickOP•2y ago

thanks for the help! I'll keep digging through the docs. we're at a point where we're not happy w/ Aurora, and evaluating other managed Postgres solutions (Supabase, Neon, etc)

garyaustin•2y ago

An example: https://github.com/orgs/supabase/discussions/4784 This shows things can be done, but sometimes it involves experimenting.

GitHub

Filter by key of array of objects · supabase · Discussion #4784

Hey, I'm trying to query my table based on the value of an object with an array of objects. My wars table looks like this: id company roster 1 "Big Coin" [{"id": "abcd&...

Sam HulickOP•2y ago

ah, very cool @garyaustin can I bug you for one quick thing I"m having trouble with?

SELECT
  "r"."id",
  CASE WHEN rd IS NOT NULL THEN
    json_build_object('id', rd.id, 'reel', rd.reel_id, 'blocks', rd.blocks, 'published', rd.published, 'version', rd.version)
  ELSE
    NULL
  END AS active_version
FROM
  "reels" AS "r"
  LEFT JOIN "reels_data" AS "rd" ON "rd"."reel_id" = "r"."id"
    AND "rd"."published" = TRUE
    AND "r"."active_version" = "rd"."version"
WHERE
  "r"."id" IN ('77f27914-b458-4883-b2dd-3a3b3fbf43f5')
ORDER BY
  "rd"."version" DESC

SELECT
  "r"."id",
  CASE WHEN rd IS NOT NULL THEN
    json_build_object('id', rd.id, 'reel', rd.reel_id, 'blocks', rd.blocks, 'published', rd.published, 'version', rd.version)
  ELSE
    NULL
  END AS active_version
FROM
  "reels" AS "r"
  LEFT JOIN "reels_data" AS "rd" ON "rd"."reel_id" = "r"."id"
    AND "rd"."published" = TRUE
    AND "r"."active_version" = "rd"."version"
WHERE
  "r"."id" IN ('77f27914-b458-4883-b2dd-3a3b3fbf43f5')
ORDER BY
  "rd"."version" DESC

I'm converting this to work w/ Supabase. it's mostly there, except the part that compares reels_data.version against reels.active_version

  const { data, error } = await supabase
    .from("reels")
    .select(
      `
        id,
        reels_data (
          id,
          reel_id,
          version,
          published
        )
      `
    )
    .eq('reels_datapublished', true)
    .eq('reels_data.version', 'active_version')
    .in("id", ["77f27914-b458-4883-b2dd-3a3b3fbf43f5"]);

  const { data, error } = await supabase
    .from("reels")
    .select(
      `
        id,
        reels_data (
          id,
          reel_id,
          version,
          published
        )
      `
    )
    .eq('reels_datapublished', true)
    .eq('reels_data.version', 'active_version')
    .in("id", ["77f27914-b458-4883-b2dd-3a3b3fbf43f5"]);

ignore that 2nd eq, I realize it's totally wrong and is comparing against an actual string, not a table column actually, that should be reels_data!left

garyaustin•2y ago

Sure pick something right off that bat it does not do... You can only filter against fixed values. The exception being if there is a foreign key relationship between two tables then obviously it will figure out the rows that match the fk relations.

Sam HulickOP•2y ago

hehe. testing the limits of the platform 😉 ok, so I'd have to create a function then

garyaustin•2y ago

Also don't forget views. They can now enforce RLS so are more useful than before. They will look just like a table to the client.

Sam HulickOP•2y ago

ahh right don't think I can use RLS since I'm not using Supabase Auth

garyaustin•2y ago

You can if you provide a jwt. People for instance use Clerk Auth with supabase and RLS.

Sam HulickOP•2y ago

oh! I'll have to look into that. I'm using AWS Cognito for auth. the front-end calls GraphQL queries, and our Lambda function resolvers get that JWT payload

garyaustin•2y ago

You can get any claim in the jwt with auth.jwt()->>'claim' to use in RLS.

Sam HulickOP•2y ago

then the Lambda function would access Supabase's DB

garyaustin•2y ago

jwt's are for the REST client.

Sam HulickOP•2y ago

hmm, I don't think RLS makes sense for our setup

garyaustin•2y ago

OK only brought it up because that was a new feature added to views in Postgres very recently and made them much more usable from auth'd clients. Views don't require RLS (well except you probably want to lock out anon access from the API).

Sam HulickOP•2y ago

yeah, I'm using the public key on the backend (locked down as a secret). I suppose i should just use the service_role key if I'm not using RLS, not really sure what the difference is

garyaustin•2y ago

That is really all it does is bypass RLS.

Sam HulickOP•2y ago

@garyaustin thanks for all your help, I appreciate it! also, I suck at Postgres so I need to learn how to write solid functions 😄

garyaustin•2y ago

The AI's do a pretty decent job on SQL.

Gaming

Programming

Using complex JSON functions

Did you find this page helpful?