Query Result Format to JSON in Oracle NoSQL Database

[otaviojava]

Hello,

I'm using the Oracle NoSQL Database, and I have a question regarding the format of query results. I have a table structure as follows:

CREATE TABLE if not exists database (id STRING, entity STRING, content JSON, primary key (id))

When I execute a query like this:

select id, entity, database.content.name ,  database.content.city  from database WHERE database.entity= 'person'

The query results return correctly, but I'd like to receive them in a JSON format. This way, I can select which fields should be included in the JSON object. I'm wondering if a function or method is available in the Oracle NoSQL Database that would allow me to format the query results as JSON. For instance, something like:

select id, entity, JSON(database.content.name ,  database.content.city) as field_name from database WHERE database.entity= 'person'

I'd appreciate any insights or guidance on how to achieve this JSON formatting of query results in the Oracle NoSQL Database.

Thank you!

[dario-vega]

Query examples

You can use the Array and Map Constructors (documented here)

select id, entity,
{
   "name":database.content.name , 
   "city":database.content.city
} as content  
from database WHERE database.entity= 'person'

More powerful transformations can be done using Sequence Transform Expressions (documented here)

select id, entity, 
seq_transform (
        database.content, 
       {"name":$.name, "city":$.city}
) as content 
from database WHERE database.entity= 'person'

Example of output

Original value

{
  "id" : "1",
  "entity" : "person",
  "content" : {
    "city" : "paris",
    "name" : "dario",
    "age" : 50
  }
}

New value (using the constructor or the transform)

{
  "id" : "1",
  "entity" : "person",
  "content" : {
    "city" : "paris",
    "name" : "dario"
  }
}

[dario-vega]

@otaviojava is it ok for you?

[otaviojava]

Thank you, @dario-vega, it works!

And documentation is super!
I have the issue of finding the information that I need.

It would be great if we aggregate the same topic, JSON, together.