Regular expression in postgresql

Question

I have the table mytable with the column images, there I store strings like JSON objects.

This column contains invalid string in some records, not because the string is incorrect, but because when I try to cast it to JSON the query fails. Example:

`SELECT images::JSON->0 FROM mytable WHERE <any filter>`

If all elements of the JSON object are good that query works successfully, but if some string has " in incorrect place (to be specific, in this case in the title key) the error happens.

Good strings are like this:

[
    {
        "imagen": "http://www.myExample.com/asd1.png",
        "amazon": "http://amazonExample.com/asd1.jpg",
        "title": "A title 1."
    },
    {
        "imagen": "http://www.myExample.com/asd2.png",
        "amazon": "http://amazonExample.com/asd2.jpg",
        "title": "A title 2."
    },
    {
        "imagen": "http://www.myExample.com/asd3.png",
        "amazon": "http://amazonExample.com/asd3.jpg",
        "title": "A title 3."
    }
]

Bad are like this:

[
    {
        "imagen": "http://www.myExample.com/asd1.png",
        "amazon": "http://amazonExample.com/asd1.jpg",
        "title": "A "title" 1."
    },
    {
        "imagen": "http://www.myExample.com/asd2.png",
        "amazon": "http://amazonExample.com/asd2.jpg",
        "title": "A title 2."
    },
    {
        "imagen": "http://www.myExample.com/asd3.png",
        "amazon": "http://amazonExample.com/asd3.jpg",
        "title": "A title 3."
    }
]

Please put attention in the difference of title keys.

I need a regular expression to convert bad strings into good ones in PostgreSQL.

Alex Netkachov · Accepted Answer · 2015-07-31 17:11:49Z

1

It will be very complicated, if possible to do this in one regexp, but it will be very easy to do in two or more.

For example, replace all the double quotes with \" and then replace {\" with {", \":\" with ":", \",\" with ",", \"} with "}. The quotes that are not escaped are the ones that breaks JSON.

Alternatively, replace "(?=[^}:]*"[\s]*}) (quotes in title only) with \" and then replace ":\" with ":". See details: https://regex101.com/r/pB6rD9/1

Crafting replace that will be able to do so in one go requires lookbehinds and I suppose that PSQL does not support them.

answered Jul 31, 2015 at 17:11

Alex Netkachov

13.6k7 gold badges56 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regular expression in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related