'in' operator does not work for a JSON field

[Stefan94V]

Link to reproduction

No response

Describe the Bug

I am using a JSON field in my collection to add any additional data as properties can differ per object in my collection.

const someCollection: ICollectionConfig = 
 {
 // ... collection props
   fields: [
        // ... other fields
            {
              name: 'additional_data',
              type: 'json',
              label: 'Extra data',
            }
          ]
    }

So far it it works nice with querying, for example I use:

 await payload.find({
    collection: 'some-collection',
       where: 'additional_data.fieldX': {
       equals: 'valY'
      }
    },
  })

But now I need to do a check where a value exists within a provided array

 await payload.find({
    collection: 'some-collection',
      where: {
      'additional_data.fieldX': {
        in: [1,2,3,4],
      },
    },
  })

And here is where I get an error:

node_modules\.pnpm\@[email protected][email protected][email protected][email protected]_w7w4zxyiztt4w5bs3ue2nosiyu\node_modules\@payloadcms\drizzle\dist\queries\parseParams.js (109:62) @ parseParams ⨯ TypeError: Cannot read properties of undefined (reading 'wildcard')

Looking at the code in the function parseParams I see that in is not supported:

const operatorKeys = {
 contains: { operator: 'like', wildcard: '%' },
 equals: { operator: '=', wildcard: '' },
 exists: { operator: val === true ? 'is not null' : 'is null' },
 like: { operator: 'like', wildcard: '%' },
 not_equals: { operator: '<>', wildcard: '' },
}

My question is this intended to not allow the in operator or is it a bug and it should be added?

To Reproduce

1. Create an collection with a JSON field:

const TestCollection: CollectionConfig = {
  slug: 'test',
  fields: [
    {
      name: 'additional_data',
      type: 'json',
      label: 'Extra data',
    },
  ],
}

2. Add a new object in the json:

  await payload.create({
    collection: 'tests',
    data: {
      additional_data: {
        fieldX: 2
      },
    },
  })

3. Query the field

  await payload.find({
      collection: 'tests',
      where: {
        'additional_data.fieldX': {
          in: [1, 2, 3],
        },
      },
    })

4. Error should throw

Payload Version

3.0.0-beta.84

Adapters and Plugins

@payloadcms/db-postgres

[Stefan94V]

Sorry I made a mistake in the issue labeling, it should be V3, not V2

[rilrom]

In the interim you could try mapping the array to the below format, it's not ideal but it does work at least.

await payload.find({
  collection: 'some-collection',
  where: {
    or: [
      {
        'additional_data.fieldX': {
          contains: 1,
        },
      },
      {
        'additional_data.fieldX': {
          contains: 2,
        },
      },
      // Rest of the array...
    ],
  },
})

[Stefan94V]

In the interim you could try mapping the array to the below format, it's not ideal but it does work at least.

await payload.find({
  collection: 'some-collection',
  where: {
    or: [
      {
        'additional_data.fieldX': {
          contains: 1,
        },
      },
      {
        'additional_data.fieldX': {
          contains: 2,
        },
      },
      // Rest of the array...
    ],
  },
})

Yes good one, I made this workaround for now if someone else has this issue:

Function that generates a list of queries

const getContainsAdditionalDataTypeQueries = (
  numbers: number[],
): { 'additional_data.type': { contains: number } }[] => {
  return numbers.map((number) => ({
    'additional_data.type': {
      contains: number,
    },
  }))
}

Just change: 'additional_data.type' to the field you want and the input numbers to the list you would like to query on

 const additionalDataTypes = getContainsAdditionalDataTypeQueries(typeValues)

  return combineQueries(baseWhere, {
    and: [
      {
        'additional_data.type': {
          exists: true,
        },
      },
      {
        or: [...additionalDataTypes],
      },
    ],
  })

[paulpopus]

^ this PR Has been merged, this feature will be available in the next release. Let me know how it goes for you!

[github-actions]

🚀 This is included in version v3.0.0-beta.104

[Stefan94V]

^ this PR Has been merged, this feature will be available in the next release. Let me know how it goes for you!

Works like a charm! Many thanks! ♥

[github-actions]

This issue has been automatically locked.
Please open a new issue if this issue persists with any additional detail.