SqlAlchemy: query to find json items that begin with a substring inside an array

Question

I have a complex json object saved on a postgresql database and I want to find all entries that contain or begin with a substring inside an array. The json object:

    "configurations" : {
  "CVE_data_version" : "4.0",
  "nodes" : [ {
    "operator" : "OR",
    "cpe_match" : [ {
      "vulnerable" : true,
      "cpe23Uri" : "cpe:2.3:a:apache:http_server:*:*:*:*:*:*:*:*",
      "versionStartIncluding" : "2.4.0",
      "versionEndIncluding" : "2.4.41"
    } ]
  } ]
}

More specifically, I want to find all objects that begin with : "cpe:2.3:a:apache" in the "cpe23Uri" field.

Query I have made:

session.query(cvemodel.data['configurations']['nodes'][0]['cpe_match'].contains([{'cpe23Uri': 'cpe:2.3:a:apache:http_server:*:*:*:*:*:*:*:*'}])).all()

Problem with this query is that, it matches the whole word. If I put this:

session.query(cvemodel.data['configurations']['nodes'][0]['cpe_match'].contains([{'cpe23Uri': 'cpe:2.3:a:apache:http_server'}])).first()

It does not return anything!

Ilja Everilä · Accepted Answer · 2020-07-14 09:54:08Z

1

If you are using PostgreSQL 12 or later, you can use jsonb_path_exists() and friends:

needle = 'cpe:2.3:a:apache:http_server'

session.query(cvemodel).\
    filter(func.jsonb_path_exists(
        cvemodel.data,
        '''$.configurations.nodes[0].cpe_match[*].cpe23Uri ?
           (@ starts with $needle)''',
        json.dumps({"needle": needle}))).\
    all()

If you want to check if cpe23Uri contains the needle, you can use the like_regex predicate, especially if you have a static "needle"; unfortunately like_regex only accepts string literals as right operand. Another option for a "contains" query would be to extract the cpe23Uri using either jsonb_path_query(), or accessors and jsonb_array_elements(), and use traditional LIKE, as described here.

edited Jul 14, 2020 at 9:54

answered Jul 13, 2020 at 16:03

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

Collectives™ on Stack Overflow

SqlAlchemy: query to find json items that begin with a substring inside an array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related