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)

p3consulting · Accepted Answer · 2024-06-17 16:07:16Z

0

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

answered Jun 17, 2024 at 16:07

p3consulting

5,4312 gold badges14 silver badges15 bronze badges

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

3 Comments

Canediguerra Over a year ago

Hi p3consulting, Thank you for your response! Unfortunately, this approach also doesn't work (no rows are returned). I've seen some solutions that suggest using an unnesting function, but it seems complex and expensive. Do you think this task can be accomplished using JSON_EXIST?

p3consulting Over a year ago

Well I don't have DB2 at hand, I tested the expression in ORACLE (23), because I know they are quite close for that, with success... select 1 where json_exists(q'~{ "randomField1": "randomValue", "outerField": [ { "innerField": "target" }, { "innerField": "dummy1" }, { "innerField": "dummy2" } ] }~', '$?(@.outerField[*].innerField == "target")') ; returns 1 row with the value 1, so at least the syntax works on the sample data

Canediguerra Over a year ago

Yes, I got it. Despite that, with DB2, this won't work. From what I've learned, JSON PATH expressions like that are unsupported for this DB (correct me if I'm wrong, future viewer who has the solution out of the box, haha). So, I tried something different (you can find it in my answer).

Canediguerra · Accepted Answer · 2024-06-18 17:38:53Z

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"}]}|

Collectives™ on Stack Overflow

IBM Db2 Query Not Returning Expected Results for JSON Array Objects

JSON Structure Example

Using Json Path & JSON_EXIST

EDIT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

JSON Structure Example

Using Json Path & JSON_EXIST

** EDIT **

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

EDIT