Filter Products based on meta data

[shunnmugam]

I've a metadata called is_popular, i need to fetch all products which is have is_popular as true

[vholik]

You should write your own listAndCount method in product service and use queryBuilder in order to retrieve based on jsonb data, it is by far the simplest solution. Here is an example how you can manage to do that:

const query = this.productRepository_
      .createQueryBuilder("product")
      .leftJoinAndSelect("product.store", "store")
      .leftJoinAndSelect("product.category", "category")
      .leftJoinAndSelect("product.variants", "variants")
      .leftJoinAndSelect("variants.prices", "prices")
      .leftJoinAndSelect("variants.options", "options")
      .leftJoinAndSelect("product.images", "images")
      .leftJoinAndSelect("product.tags", "tags")
      .leftJoinAndSelect("product.collection", "collection")
      .leftJoinAndSelect("product.type", "type")
      .select([]) // here is where you set what fields do you want to retrieve
      .where("product.status = 'published'");
      
    // Your logic goes here
    if (is_popular) {
      query.andWhere(`product.metadata->>'is_popular' = 'true'`);
    }
    
    // and then get products
    const products = await query.getMany()

[BorisKamp]

You should write your own listAndCount method in product service and use queryBuilder in order to retrieve based on jsonb data, it is by far the simplest solution. Here is an example how you can manage to do that:

const query = this.productRepository_
      .createQueryBuilder("product")
      .leftJoinAndSelect("product.store", "store")
      .leftJoinAndSelect("product.category", "category")
      .leftJoinAndSelect("product.variants", "variants")
      .leftJoinAndSelect("variants.prices", "prices")
      .leftJoinAndSelect("variants.options", "options")
      .leftJoinAndSelect("product.images", "images")
      .leftJoinAndSelect("product.tags", "tags")
      .leftJoinAndSelect("product.collection", "collection")
      .leftJoinAndSelect("product.type", "type")
      .select([]) // here is where you set what fields do you want to retrieve
      .where("product.status = 'published'");
      
    // Your logic goes here
    if (is_popular) {
      query.andWhere(`product.metadata->>'is_popular' = 'true'`);
    }
    
    // and then get products
    const products = await query.getMany()

@vholik based on your answer Im trying the following:

import { 
  Order,
  type MedusaRequest, 
  type MedusaResponse,
} from "@medusajs/medusa"
// import { Post } from "../models/order"
import { EntityManager } from "typeorm"

export const GET = async (
  req: MedusaRequest, 
  res: MedusaResponse
) => {
  const manager: EntityManager = req.scope.resolve("manager")
  const orderRepo = manager.getRepository(Order)

  return res.json({
    orders: await orderRepo.createQueryBuilder("order")
    .where(`order.metadata->>'reference' = '23005344'`)
    .getMany()
  })
}

but Im getting a QueryFailedError: syntax error at or near "order" error. My order's metadata object is:

"metadata": {
                "invoice": {
                    "url": "https://cxvcxv024_133_60e06139a8.pdf",
                    "name": "2024.133"
                },
                "message": "levering voor 9-2-2024",
                "reference": "23005344",
                "creator_id": "2",
                "invoice_emails": [
                    "invoices@sdfxbvfbc.nl"
                ],
                "pipedrive_deal_id": 4070,
                "birdblocker_company": {
                    "id": "2",
                    "bic": "RABONL2U",
                    "rex": "NLREX6171",
                    "city": "Den Hoorn",
                    "eori": "NL8596.30.493",
                    "iban": "NL 87 RABO 0337 8612 26",
                    "logo": {
                        "alt": null,
                        "url": "https://birdblocker-admin-bucket.s3.eu-central-1.amazonaws.com/birdblocker_logo_square_5c86c0ab95.svg",
                        "name": "birdblocker-logo-square.svg"
                    },
                    "name": "BirdBlocker",
                    "slug": "bb",
                    "email": "order@birdblocker.com",
                    "phone": "+31 174 725 725",
                    "address": "Hooipolderweg 20",
                    "country": "The Netherlands",
                    "website": "birdblocker.com",
                    "zip_code": "2635 CZ",
                    "coc_number": "KvK12345678",
                    "vat_number": "NL8596.30.493.B01",
                    "invoice_slug": "{year}.{number}",
                    "contact_person": "P.L. van der Ven"
                },
                "manual_payment_term": "afterpay_14",
                "stripe_payment_method": null,
                "strapi_order_relation_id": 1265,
                "sent_invoice_to_account_email_address": false
            },

Say I want to query for orders where metadata.birdblocker_company.slug is bb (like this order), how would I do that?

[alexandrosk]

@shunnmugam any luck with this one? im looking forward to add filters on my storefront but looks so hard so far :/

[BorisKamp]

I have a solution for orders, should be similair to products:

src/api/admin/orders/filter-metadata/route.ts

import { 
  Order,
  type MedusaRequest, 
  type MedusaResponse,
} from "@medusajs/medusa"
import { skip } from "node:test"
// import { Post } from "../models/order"
import { EntityManager, JsonContains } from "typeorm"

export const GET = async (
  req: MedusaRequest, 
  res: MedusaResponse
) => {
  const manager: EntityManager = req.scope.resolve("manager")
  const orderRepo = manager.getRepository(Order)

  const jsonSearchObject = {}

  // BC order id
  const bc_order_id = req.query?.bc_order_id as string
  if (bc_order_id) {
    jsonSearchObject['bc_order_id'] = parseInt(bc_order_id)
  }

  // Incoterms
  const incoterms = req.query?.incoterms as string
  if (incoterms) {
    jsonSearchObject['incoterms'] = incoterms
  }

  const skip = req.query.offset ?? 0
  const take = req.query.limit ?? 0

  const relations = []
  if (req.query.expand) {
    const expand = req.query.expand as string
    const expandArray = expand.split(',')
    for (let i = 0; i < expandArray.length; i++) {
      relations.push(expandArray[i])
    }
  }

  const [orders, count] = await orderRepo.findAndCount({
    where: {
      metadata: JsonContains(jsonSearchObject)
    },
    relations,
    skip: skip as number,
    take: take as number,
  })

  return res.json({
    orders,
    count
  })
}

What it does is the following:

1. It gets the order repo (https://docs.medusajs.com/development/entities/repositories)
2. Start with an empty search object
3. Checks for my two custom filter query params and sets the search params needed
4. Do some pagination and relation stuff
5. Uses the order repo to fetch the orders with the where clause.

However, I did find it very convenient to extend the order model so that the filter could be used in the core endpoints that exist already.

What I would do in your case is extend the product with the fields required (https://docs.medusajs.com/development/entities/extend-entity)

If you need any more help let me know, I'll try to help.

[voxsar]

For some reason the v1 did not have the ability to set metadata for product options and their values, I have created a plugin that allows to add such metadata

Use Cases, lets say you want to store the option's image or the option's values image like

E.g. A shoe product with color option, each option value can have meta data for a variant image (red, blue etc), (which can also be done using variant images, but it's just an example)

Another example is using the meta data to inform how the react front end should render the options, a select box, radio buttons

/admin/products/{id}/options/{id}/option

{
metadata: {
inputtype: 'select'
}

I made this plugin that extends the functionality of the medusa 1.x to allow adding metadata to options and values