Drizzle Team•2mo ago

How to select a subset of fields from a JSONB?

I'm using Postgres and can't figure out how to select a subset of JSONB fields? Is this supported yet?

11 Replies

Angelelz•2mo ago

There are JSON operators in PG that you can use

Angelelz•2mo ago

https://www.postgresql.org/docs/9.5/functions-json.html

PostgreSQL Documentation

JSON Functions and Operators

AnshulOP•2mo ago

Thanks @Angelelz. I'm aware of the ->> operator which is how I handle this outside of Drizzle. I have several working examples of this but Drizzle doesn't seem to like this syntax. What am I doing wrong here assuming data is a JSONB type and the intention is to pull out just the agency field?

db.select({
  id: opp.id,
  data: `opp.data->>agency`
})

db.select({
  id: opp.id,
  data: `opp.data->>agency`
})

Tried this as well with no luck.

select(sql`id, data->>agency`)

select(sql`id, data->>agency`)

Angelelz•2mo ago

Oh, that's just cause you use the sql operator per column:

db.select({
  id: opp.id,
  data: sql<string>`opp.data->>agency` // string or whatever type you expect from agency
})

db.select({
  id: opp.id,
  data: sql<string>`opp.data->>agency` // string or whatever type you expect from agency
})

Or how you'd usually see it in drizzle:

db.select({
  id: opp.id,
  data: sql<string>`${opp.data}->>agency` // string or whatever type you expect from agency
})

db.select({
  id: opp.id,
  data: sql<string>`${opp.data}->>agency` // string or whatever type you expect from agency
})

BTW, @rphlmr ⚡ is an expert in JSON data in postgres, he has a couple helpers for this type of stuff

AnshulOP•2mo ago

Thanks again @Angelelz. The above two syntaxes didn't work but the one below inspired by your reply actually worked. @rphlmr ⚡ would you be able to help here? Is there an easy way to not have to spell out each field individually?

select({
  id: opp.id,
  data: {
    agency: sql<string>`opp.data->>'agency'`,
    url: sql<string>`opp.data->>'url'`
  }
})

select({
  id: opp.id,
  data: {
    agency: sql<string>`opp.data->>'agency'`,
    url: sql<string>`opp.data->>'url'`
  }
})

This is from Supabase and it returns the selected fields keeping the data field as an object.

select(
  `id, data->>agency, data->>url`
)

select(
  `id, data->>agency, data->>url`
)

Expected return format

{ id, data: { agency, url } }

{ id, data: { agency, url } }

Angelelz•2mo ago

This is not my area, I've never been a fan of saving json in a relational db. But I think I found some of the stuff that he shared: https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15#file-utils-ts-L60

Gist

Drizzle snippets

Drizzle snippets. GitHub Gist: instantly share code, notes, and snippets.

AnshulOP•2mo ago

Thanks for finding and sharing this. Some pretty cool stuff here!

rphlmr ⚡•2mo ago

hey sorry it was bed time for me 😄

rphlmr ⚡•2mo ago

here an example: https://drizzle.run/wgnd28shd0f8hpmhyxnpisew

Drizzle Run

Select field in jsonb - Drizzle Run

rphlmr ⚡•2mo ago

select({
  id: opp.id,
  data: {
    agency: sql<string>`(${opp.data}->'agency')`,
    url: sql<string>`(${opp.data}->'url')`
  }
})

select({
  id: opp.id,
  data: {
    agency: sql<string>`(${opp.data}->'agency')`,
    url: sql<string>`(${opp.data}->'url')`
  }
})

note de () and the string interpolation on your table.field -> => get the value ->> => get the value as string

AnshulOP•2mo ago

Thanks @rphlmr ⚡ that's exactly what I ended up with based on the feedback I was provided. Your GitHub gist had some really cool functions in it!

Gaming

Programming

How to select a subset of fields from a JSONB?