Postgres perform regex query on jsonb field-CodePudding

I have a column in my Postgres database that stores jsonb type values. Some of these values are raw strings (not a list or dictionary). I want to be able to perform a regex search on this column, such as

select * from database where jsonb_column::text ~ regex_expression.

The issue is that for values that are already strings, converting from jsonb to text adds additional escaped double quotes at the beginning and end of the value. I don't want these included in the regex query. I understand why Postgres does this, but if, say we assume all values stored in the jsonb field were jsonb strings, is there a work around? I know you can use ->> to get a value out of a jsonb dictionary, but can't figure out a solution for just jsonb strings on their own.

Once I figure out how to make this query in normal Postgres, I want to translate it into Peewee. However, any and all help with even just the initial query would be appreciated!

CodePudding user response：

Just cast the json to text. Here is an example:

class Reg(Model):
    key = CharField()
    data = BinaryJSONField()
    class Meta:
        database = db

for i in range(10):
    Reg.create(key='k%s' % i, data={'k%s' % i: 'v%s' % i})

# Find the row that contains the json string "k1": "v1".
expr = Reg.data.cast('text').regexp('"k1": "v1"')
query = Reg.select().where(expr)
for row in query:
    print(row.key, row.data)

Prints

k1 {'k1': 'v1'}

CodePudding user response：

To extract a plain string (string primitive without key name) from a JSON value (json or jsonb), you can extract the "empty path" like:

SELECT jsonb '"my string"' #>> '{}';

This also works for me (with jsonb but not with json), but it's more of a hack:

SELECT jsonb '"my string"' ->> 0

So:

SELECT * FROM tbl WHERE (jsonb_column #>> '{}') ~ 'my regex here';