Skip to Main Content
IBM System Storage Ideas Portal


This portal is to open public enhancement requests against IBM System Storage products. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas
  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Status Not under consideration
Workspace Storage Discover
Created by Guest
Created on Dec 20, 2021

Using DB2 JSON functionality in Spectrum Discover

We've found a need for more complex tag values in our use cases for Spectrum Discover. An example of this is the ability to associate an entity with a document, where the entity has its own attributes. One such entity could be a "creator", with attributes "name", "email", "role" etc. JSON documents are perfect for this case, and some experimentation indicates that tag values support JSON-formatted values, such as:
{"name": "Jane Doe", "email": "john@doe.com", "role": "dataset_contact"}
where the tag name is 'test_json'.

Using the DB2 documentation for JSON_VALUE [1] we've verfied that querying the attributes of a JSON-formatted tag value is possible in Spectrum Discover. It works in the SQL query section of the GUI, the db2whrest/search and db2whrest/sql_query API endpoints. A working query for the above JSON-string:
"JSON_VALUE(test_json, 'strict $.name' RETURNING VARCHAR(32)) LIKE '%Doe'"
Which will return any object in the database where the 'name' attribute of the document matches.

However, the DB2 documentation also suggests that it should be possible to perform more complex queries than this. In particular, we would like to query tag values with more than one document.

Example:

A tag value similar to the one above, but with multiple instances of the same type of document.
It could look like this:
"{
creators = [
{
"name": "Jane Doe",
"email": "jane@doe.com",
"role": "dataset_contact"
},
{
"name": "John Doe",
"email": "john@doe.com",
"role": "author"
},
{
"name": "Jack Doe",
"email": "jack@doe.com",
"role": "dataset_contact"
}
]
}"

Querying with JSON_VALUE won't work in this instance. We've made some attempts using JSON_TABLE [2], with limited success.
For example:

SELECT t.name, t.email, t.role
FROM T_TEST_JSON,
JSON_TABLE(
T_TEST_JSON.value,
'strict $'
COLUMNS(
name VARCHAR(2048) FORMAT JSON PATH 'lax $.creators[*].name' WITH CONDITIONAL WRAPPER,
email VARCHAR(2048) FORMAT JSON PATH 'lax $.creators[*].email' WITH CONDITIONAL WRAPPER,
role VARCHAR(2048) FORMAT JSON PATH 'lax $.creators[*].role' WITH CONDITIONAL WRAPPER
) ERROR ON ERROR
) as t

will yield the following result when sent to the API endpoint /db2whrest/sql_query:

0,"[""Jane Doe"",""John Doe"",""Jack Doe""]","[""jane@doe.com"",""john@doe.com"",""jack@doe.com""]","[""dataset_contact"",""author"",""dataset_contact""]"

while what we would like to see is a something like:
0,""Jane Doe"",""jane@doe.com"",""dataset_contact""
1,""John Doe"",""john@doe.com"",""author""
2,""Jack Doe"",""jack@doe.com"",""dataset_contact""

on which we could apply a WHERE clause to further filter the results.

We've been unable to find any documentation or examples of JSON capabilities in the Spectrum Discover documentation. Are there any resources other than the DB2 documentation available for this? Or perhaps some examples from usage in Spectrum Discover?


[1] https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-value
[2] https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-table
[3] https://www.ibm.com/docs/en/i/7.3?topic=data-using-json-table

Idea priority Medium