JSON "subscription" syntax (since Postgres 14)

[ivan-kleshnin]

From https://aaronbos.dev/posts/postgres-14-json

-- Add a jsonb key/value via update when the key doesn't exist already
UPDATE artist
-- "occupation"" key will be added to all documents with value "artist"
SET document['occupation'] = '"artist"';

---

Is it possible to generate this type of query without falling back to raw SQL?

Here's my working code:

await dz.execute(sql`
  UPDATE ${userTable} 
  SET data['recentActivity'] = '{"contributionCalendar": {"totalContributions": 42}}'
  WHERE ${userTable.login} = ${"someLogin"}
`)

Query builder seemingly does not support sql in .set():

await dz.update(userTable)
  .set(sql`...`) // not supported

Do I miss something or it's really not supported as, perhaps, something Postgres specific?

[gabrielDonnantuoni]

I'd love jsonb Subscripting support too

[kravetsone]

u can fallback to sql on key level like .set({key: sql``)}

[gabrielDonnantuoni]

Yes, that's true. But the issue is that we would need the key to be something like data['recentActivity'], following @ivan-kleshnin example, for us to actually use the subscripting functionality which is not allowed today.

[kravetsone]

data['recentActivity']

does

await dz.update(userTable)
  .set({
  "data['recentActivity']": sql`'{"contributionCalendar": {"totalContributions": 42}}'`
  })

wont work?

[kravetsone]

data['recentActivity']

does

await dz.update(userTable)
  .set({
  "data['recentActivity']": sql`'{"contributionCalendar": {"totalContributions": 42}}'`
  })

wont work?

oh yeah looks like it didt work

[gabrielDonnantuoni]

Yeah, currently the method only accept keys that match a table column.