Retrieve particular key from postgresql jsonb using knex

Question

I have uploads_file table with formats field i need to retrieve only formats.thumbnail

upload_file Schema

Column  Type    
id  integer Auto Increment [nextval('upload_file_id_seq')]  
name    character varying(255)  
formats jsonb NULL

inside strapi i am using knex in the following way to retrive formats.

const knex = strapi.connections.default
const resp = await knex
          .select("upf.formats")
          .from("upload_file AS upf")  
return resp

it results in

[
  {
    "formats": {
      "large": {
        "ext": ".png",
        "url": "/uploads/large_sld1_acb6efc9cc.png",
        "hash": "large_sld1_acb6efc9cc",
        "mime": "image/png",
        "name": "large_sld1.png",
        "path": null,
        "size": 312.82,
        "width": 1000,
        "height": 564
      },
      "small": {
        "ext": ".png",
        "url": "/uploads/small_sld1_acb6efc9cc.png",
        "hash": "small_sld1_acb6efc9cc",
        "mime": "image/png",
        "name": "small_sld1.png",
        "path": null,
        "size": 96.62,
        "width": 500,
        "height": 282
      },
      "medium": {
        "ext": ".png",
        "url": "/uploads/medium_sld1_acb6efc9cc.png",
        "hash": "medium_sld1_acb6efc9cc",
        "mime": "image/png",
        "name": "medium_sld1.png",
        "path": null,
        "size": 192.46,
        "width": 750,
        "height": 423
      },
      "thumbnail": {
        "ext": ".png",
        "url": "/uploads/thumbnail_sld1_acb6efc9cc.png",
        "hash": "thumbnail_sld1_acb6efc9cc",
        "mime": "image/png",
        "name": "thumbnail_sld1.png",
        "path": null,
        "size": 29.8,
        "width": 245,
        "height": 138
      }
    }
  }
]

the query works fine. but i need to retrieve only thumbnail out of it. since it is stored as jsonb format not aware of how to retrieve it.

Thanks in Advance.

Answer 1

You can use -> operator

const knex = strapi.connections.default
const resp = await knex
          .select(
            knex.raw(`upf.formats->'thumbnail' as thumbnail`)
          )
          .from("upload_file AS upf")  
return resp