[Documentation] Clarification on Querying JSON Values and Indexing

[otaviojava]

I am seeking clarification on how to query JSON values in Oracle NoSQL Database and whether indexing is mandatory for such queries. The documentation provides indexing information but needs more clarity on the essential details for querying JSON values.

I have read the documentation and explored a sample JSON document as provided below:

{
    "account": 1,
    "person": {
        "lastName": "Jones",
        "firstName": "Joe",
        "address": {
            "home": {
                "street": "15 Elm",
                "city": "Lakeville",
                "zip": "12345"
            },
            "work": {
                "street": "12 Main",
                "city": "Lakeville",
                "zip": "12345"
            }
        },
        "phone": {
            "home": "800-555-1234",
            "work": "877-123-4567"
        }
    }
}

I have attempted various queries, including the one below, without success:

select * from database WHERE 'person.lastName' = 'Jones'

Please provide clear guidance on how to query JSON values in the Oracle NoSQL Database and clarify whether indexing is mandatory for such queries. Any examples or code snippets illustrating the correct usage would be greatly appreciated.

[dario-vega]

Hi @otaviojava ,

If you are using JSON columns, you need to specify an alias for the table. Indexes are not mandatory

sql-> create table database (account integer, person json, primary key (account));
Statement completed successfully

The query provided above compares 2 strings.

sql-> select * from database WHERE 'person.lastName' = 'Jones';
0 row returned

The good query is adding an alias

sql-> select * from database as $p WHERE $p.person.lastName = 'Jones';
{
  "account" : 1,
  "person" : {
    "address" : {
      "home" : {
        "city" : "Lakeville",
        "street" : "15 Elm",
        "zip" : "12345"
      },
      "work" : {
        "city" : "Lakeville",
        "street" : "12 Main",
        "zip" : "12345"
      }
    },
    "firstName" : "Joe",
    "lastName" : "Jones",
    "phone" : {
      "home" : "800-555-1234",
      "work" : "877-123-4567"
    }
  }
}

You can find information about queries in the Developers Guide

• https://docs.oracle.com/en/database/other-databases/nosql-database/23.3/nsdev/using-path-expressions1.html

You can find information about the indexes in the Developers Guide

• https://docs.oracle.com/en/database/other-databases/nosql-database/23.3/nsdev/create-and-manage-indexes1.html

You can find all the details in the SQL Reference Guide

• https://docs.oracle.com/en/database/other-databases/nosql-database/23.3/sqlreferencefornosql/path-expressions.html

Let us know if you need more information

[dario-vega]

For completeness only, You can also use the default alias for a table, the table name itself

sql-> select * from database  WHERE database.person.lastName = 'Jones';
{
  "account" : 1,
  "person" : {
    "address" : {
      "home" : {
        "city" : "Lakeville",
        "street" : "15 Elm",
        "zip" : "12345"
      },
      "work" : {
        "city" : "Lakeville",
        "street" : "12 Main",
        "zip" : "12345"
      }
    },
    "firstName" : "Joe",
    "lastName" : "Jones",
    "phone" : {
      "home" : "800-555-1234",
      "work" : "877-123-4567"
    }
  }
}

Extract from the documentation

SELECT id, u.address.city
FROM users u;

Notice that if the input to a path expression is a table column, a table alias must be used together with the column name. Otherwise, as explained in the Variable References section, an expression like address.city would be interpreted as a reference to the city column of a table called address, which is of course not correct.

[otaviojava]

Thank you for the links!