Postgres - Modify JSON to Retain Only Keys Where Values Include a Matching String

25 views Asked by At

Background: I am currently running postgres 9.6. I have a table named parcels. Parcels has two columns. The first column is "address" and the second column is "details" which is JSON that contains attributes about about the parcel. Below is an example of "details" json:

{
    "type": "land",

    "approvals":
    {
        "city": "Example City",
        "form": "/60351caba45b7481831f009233320cf830815.pdf" 
    },
    "propertyDescription":
    {
        "land": [
        {
            "map": "/481831f00923332.pdf",
            "lat": 0.00000,
            "lon": 0.00000,

        }],
        "improvements": [
        {
            "plan": "/static6c14c78ffb64.pdf",
            "value": 240000,
        },
        {
            "offsets": "/512b334dd4208477f49ed0d78b2ce3f5222d27.pdf",
            "current": true,
        }]
    },
    "forms": ["consent.docx","survey.pdf","surveysubmit.docx"],
    "request_format": ".pdf"
}

Question: I need to write a postgres select query that returns both columns but modifies the "details" json to ONLY include keys where the value contains ".pdf". This includes removing non-matching elements from arrays and keys from nested objects. The final output of the "details" column needs to look something like this:

{
    "approvals":
    {
        "form": "/60351caba45b7481831f009233320cf830815.pdf" 
    },
    "propertyDescription":
    {
        "land": [
        {
            "map": "/481831f00923332.pdf",
        }],
        "improvements": [
        {
            "plan": "/static6c14c78ffb64.pdf",
        },
        {
            "offsets": "/512b334dd4208477f49ed0d78b2ce3f5222d27.pdf",
        }]
    },
    "forms": ["survey.pdf"],
    "request_format": ".pdf"
}

I do not own this dataset and do not always know the keys to parse.

I have referenced the postgres doucmentation: https://www.postgresql.org/docs/9.6/functions-json.html. I have used jsonb_each but run into the issue of mixed value types. I do not know how to parse out the array and then the nested objects to perform a like.

0

There are 0 answers