Search in JSON columns

[danharrin]

Depending on the driver and situation, json_extract() or ::text/->> needs to be used to search JSON columns.

Sometimes, I want to search the entire JSON (json_extract(column, '$') / column::text), and sometimes I just want to search inside a specific key (json_extract(column, "$.json_key") / column->>'json_key'). I think that one expression could be abstracted to handle all of these cases.

Searches should also work with case insensitivity, as per #5.

[tpetry]

Can you explain in which cases you want to search an entire JSON? So you want to search the JSON as a string, not an object/array?

And in which cases you need to use json_extract() or ::text/->>?

[danharrin]

Searching the entire JSON is useful for when there are translations stored in the column as JSON. So en, es, nl keys and you want to search inside all of those keys at once, across languages. This is when I would use (json_extract(column, '$') / column::text, which both search the entire JSON as a string at once.

I use json_extract(column, "$.json_key") / column->>'json_key' when I want to search inside a specific key only.

[tpetry]

Hmm, on MySQL the json_extract(column, '$') call is unnecessary, as you can always use a JSON column like a string. But PostgreSQL would throw a type error.

This behavior could definitely be harmonized for all engines with an expression.

[danharrin]

Hmm, I wonder why json_extract(column, '$') is used in the Filament codebase then.

[danharrin]

I think I can probably remove that bit then. Thanks. But since a different syntax needs to be used for Postgres, an abstraction is probably still relevant. Thoughts?

[tpetry]

I'll have a look at whether I can do the JSON extraction in pure SQL (every database has some way of doing it) or whether I have to emulate this by building custom SQL operators for every db.