Couchbase N1QL Update complex-structured JSON document

Question

Intro

Hi! After using Spring Startup Actuator endpoint, I recieved the data about steps my application took to start. It's a JSON response, and I'd like to modify it a little, to make it more readable. Since I use CouchBase in my project, I though it probably will be the easiest way to modify big JSON with complex structure. I need a help with writing a N1QL Query to modify my JSON.

Prerequisites

I have a JSON document with this structure loaded to a bucket in my CouchBase (I know it's pretty big, so I kindly ask you to have patience and try to understand it)

{
  "springBootVersion": "2.4.7",
  "timeline": {
    "startTime": {
      "nano": 909000000,
      "epochSecond": 1627565536
    },
    "events": [
      {
        "endTime": {
          "nano": 666000000,
          "epochSecond": 1627566426
        },
        "duration": {
          "seconds": 481,
          "nano": 751000000,
          "units": [
            "SECONDS",
            "NANOS"
          ],
          "negative": false,
          "zero": false
        },
        "startTime": {
          "nano": 666000000,
          "epochSecond": 1627566426
        },
        "startupStep": {
          "name": "spring.beans.instantiate",
          "id": 2480,
          "tags": [
            {
              "key": "beanName",
              "value": "hystrix-configuration"
            },
            {
              "key": "beanType",
              "value": "class org.apache.camel.model.HystrixConfigurationDefinition"
            },
            {
              "key": "exception",
              "value": "class org.springframework.beans.factory.NoSuchBeanDefinitionException"
            },
            {
              "key": "message",
              "value": "No bean named 'hystrix-configuration' available"
            }
          ],
          "parentId": 4
        }
      },
      {
        ...
      }
    ]
  }
}

The problem

What I need is to format that JSON document in a following way:

I would like to exclude fields "springBootVersion", "timeline" and leave only the "events" array of objects.
In each object inside the "events" array I need to remove fields "startTime", "endTime", and inside object "duration" I want to remove fields "units", "zero", "negative".
Also, if possible, I'd like to modify field nanoseconds to milliseconds,by divisioning it's values by 1 000 000 and changing the name from "nanoseconds" to "miliseconds".
This can be Update or Select type of query, I don't really mind.
(Added after I got the answer) I would also like to sort an array of objects by field events.duration.seconds and events.duration.miliseconds (in case seconds are equal for 2 objects)

Desired result

As the result after modification of JSON object should look like this:

[
  {
    "duration": {
      "seconds": 481,
      "miliseconds": 751
    },
    "startupStep": {
      "name": "spring.beans.instantiate",
      "id": 2480,
      "tags": [
        {
          "key": "beanName",
          "value": "hystrix-configuration"
        },
        {
          "key": "beanType",
          "value": "class org.apache.camel.model.HystrixConfigurationDefinition"
        },
        {
          "key": "exception",
          "value": "class org.springframework.beans.factory.NoSuchBeanDefinitionException"
        },
        {
          "key": "message",
          "value": "No bean named 'hystrix-configuration' available"
        }
      ],
      "parentId": 4
    }
  },
  {
    ...secondOjbect
  },
  {
    ...ThirdOjbect
  }
]

I'm pretty new to couchBase and N1QL especially, so I was able to update only a simple-structured JSON objects. Kindly ask for your help.

*Note: If you know a simpler and easier way to modify JSON for my needs, please share it.

vsr vsr · Accepted Answer · 2021-07-31T14:06:24

SELECT 
   ARRAY {e.startupStep, 
          "duration":{e.duration.seconds, 
                      "miliseconds": e.duration.nanosconds/1000000
                     }
         } 
   FOR e IN d.timeline.events 
   END AS events
FROM default AS d
WHERE .......

If you want update like {"events":[ ]}

UPDATE default AS d
SET d = {"events": ARRAY {e.startupStep, 
                          "duration":{e.duration.seconds, 
                                      "milliseconds": e.duration.nanosconds/1000000
                                     }
                          } 
                   FOR e IN d.timeline.events 
                   END 
        }
WHERE .......

Sort events by seconds ARRAY

SELECT (SELECT e.startupStep,
               {e.duration.seconds, milliseconds} AS duration
        FROM d.timeline.events AS e
        LET milliseconds = e.duration.nanosconds/1000000
        ORDER BY e.duration.seconds, milliseconds
       ) AS events
FROM default AS d
WHERE .......