IBM Db2 Query Not Returning Expected Results for JSON Array Objects

Question

I'm working with IBM Db2 (Version 11.5) and I'm trying to query JSON data stored in a column named data which contains an array of objects. However, my query using JSON_EXISTS does not return the expected results.

JSON Structure Example

The JSON structure (simplified here) includes an array outerField, and I need to retrieve rows in which an object of outerField exists with innerField equal to "target". However, my query using JSON_EXISTS does not return the expected results.

The simplified JSON data stored in the data column looks like this:

{
  "randomField1": "randomValue",
  ...
  "outerField":
    [
      {
        "innerField": "target"
      },
      {
        "innerField": "dummy1"
      },
      {
        "innerField": "dummy2"
      }
    ]
}

Using Json Path & JSON_EXIST

I would like to use JSON Path, and using an online evaluation tool, I've found that this query should work:

$.outerField[?(@.innerField== "target")].

Given that I can't use directly JSON_VALUE with a json path like:

JSON_VALUE(data, '$.outerField[*].innerField') = 'target'

Since JSON_VALUE expects just a single value (outerField[0].innerFieldworks as expected).

I've thought that I could use Db2's JSON_EXISTS function:

SELECT *
FROM test_entity
WHERE JSON_EXISTS(
    data,
    '$.outerField[?(@.innerField== "target")]'
)

That, unfortunately, does not return any rows even though the example JSON clearly shows an object in outerField with innerField equal to "target".

Any ideas or suggestion?

** EDIT **

I think the most reasonable solution is to use the JSON_TABLE function to unnest the inner array, but I'm not sure how to proceed. I'm able to use JSON_TABLE on ordinary fields, but with arrays, I'm getting many syntax errors. For instance:

SELECT *
FROM TEST_ENTITY te, JSON_TABLE(te.data, 'lax $.outerField[*]' columns (target VARCHAR(100) PATH 'lax $.innerField') ERROR ON ERROR)  AS jt 
WHERE jt.innerField= 'target';

This query returns the following error

SQL Error [42601]: An unexpected token "lax $.outerField[*]" was found following "".  Expected tokens may include:  "strict $".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.31.10

(Even using strict instead of lax I'm receiving the same error)

Answer 1

Given my frustration with the (bad, really bad) DB2-related documentation, I managed this task in a somewhat "fancy" way.

I’m not sure if this is the best solution, but it works for now. I've figured out that I could use JSON_QUERY to extract and flatten nested arrays in a JSON object.

Just like this:

SELECT JSON_QUERY(tt.JSONDOC, '$.outerField[*].innerField' WITH UNCONDITIONAL WRAPPER)
FROM TEST_TABLE tt

This query returns:

-------------------+
["targetB"]        |
["target"]         |
["dummy1"]         |
["dummy2"]         |
["target","dummy3"]|

As you can see, the result contains the flattened CLOB of the innerField values for each rows. So I've realized that I could use this approach in the WHERE clause to filter the rows as needed from the beginning.

Here’s the resulting query:

SELECT *
FROM EMPLOYEE_TABLE et
WHERE JSON_QUERY(et.JSONDOC, '$.outerField[*].innerField' WITH UNCONDITIONAL WRAPPER) LIKE '%"target"%'

That returns all the rows with at least one "innerField" equal to the "target" value!

ID|JSONDOC                                                                                                                                                                 |
--+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 2|{"id" : 901,"firstname" : "John","lastname"  : "Doe","phoneno"   : "555-3762",¶ "outerField" : [{  "innerField": "target"}]}                                            |
 5|{"id" : 901,"firstname" : "John","lastname"  : "Doe","phoneno"   : "555-3762",¶ "outerField" : [{  "innerField": "target"},{  "innerField": "dummy3"}]}|