Access to nested documents by property value

SQL++
1

Hello,

I am beginning to use Couchbase and I am having a few problems.
We have json with many nested information and we want to access parts by property values.

The information is like that

[
  {
    "composition": {
      "content": {
        "archetype_details": {
          "archetype_id": {
            "class": "ARCHETYPE_ID",
            "value": "openEHR-EHR-COMPOSITION.encounter.v1"
          },
          "class": "ARCHETYPED",
          "rm_version": "1.0.1",
          "template_id": {
            "class": "TEMPLATE_ID",
            "value": "Vital Signs"
          }
        },
        "archetype_node_id": "openEHR-EHR-COMPOSITION.encounter.v1",
        "category": {
          "class": "DV_CODED_TEXT",
          "defining_code": {
            "class": "CODE_PHRASE",
            "code_string": "433",
            "terminology_id": {
              "class": "TERMINOLOGY_ID",
              "value": "openehr"
            }
          },
          "value": "event"
        },
        "class": "COMPOSITION",
        "composer": {
          "class": "PARTY_IDENTIFIED",
          "name": "ehrscape"
        },
        "content": [
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.body_temperature.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.body_temperature.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0002",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0003",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0001",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Temperature"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 39.7,
                          "precision": 1,
                          "units": "°C"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "Single"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "state": {
                    "archetype_node_id": "at0029",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0030",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Body exposure"
                        },
                        "value": {
                          "class": "DV_CODED_TEXT",
                          "defining_code": {
                            "class": "CODE_PHRASE",
                            "code_string": "at0031",
                            "terminology_id": {
                              "class": "TERMINOLOGY_ID",
                              "value": "local"
                            }
                          },
                          "value": "Naked"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "State"
                    }
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "History"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Body temperature"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.blood_pressure.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.blood_pressure.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0001",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0006",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0003",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Systolic"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 105,
                          "precision": 0,
                          "units": "mm[Hg]"
                        }
                      },
                      {
                        "archetype_node_id": "at0005",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Diastolic"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 84,
                          "precision": 0,
                          "units": "mm[Hg]"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "blood pressure"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "history"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Blood Pressure"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.height.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.height.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0001",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0002",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0003",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Body Height/Length"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 84.60156104434435,
                          "units": "cm"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "Simple"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "history"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Height/Length"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.body_weight.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.body_weight.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0002",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0003",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0001",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Body weight"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 10.9,
                          "units": "kg"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "Simple"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "history"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Body weight"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.heart_rate-pulse.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.heart_rate-pulse.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0002",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0003",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0001",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Rate"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 85,
                          "precision": 0,
                          "units": "/min"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "structure"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "history"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Pulse"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.respiration.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.respiration.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0001",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0002",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0003",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0004",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "Rate"
                        },
                        "value": {
                          "class": "DV_QUANTITY",
                          "magnitude": 15,
                          "precision": 0,
                          "units": "/min"
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "List"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "history"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Respirations"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          },
          {
            "archetype_details": {
              "archetype_id": {
                "class": "ARCHETYPE_ID",
                "value": "openEHR-EHR-OBSERVATION.indirect_oximetry.v1"
              },
              "class": "ARCHETYPED",
              "rm_version": "1.0.1"
            },
            "archetype_node_id": "openEHR-EHR-OBSERVATION.indirect_oximetry.v1",
            "class": "OBSERVATION",
            "data": {
              "archetype_node_id": "at0001",
              "class": "HISTORY",
              "events": [
                {
                  "archetype_node_id": "at0002",
                  "class": "POINT_EVENT",
                  "data": {
                    "archetype_node_id": "at0003",
                    "class": "ITEM_TREE",
                    "items": [
                      {
                        "archetype_node_id": "at0006",
                        "class": "ELEMENT",
                        "name": {
                          "class": "DV_TEXT",
                          "value": "spO2"
                        },
                        "value": {
                          "class": "DV_PROPORTION",
                          "denominator": 100,
                          "numerator": 96.2,
                          "type": 0
                        }
                      }
                    ],
                    "name": {
                      "class": "DV_TEXT",
                      "value": "Tree"
                    }
                  },
                  "name": {
                    "class": "DV_TEXT",
                    "value": "Any event"
                  },
                  "time": {
                    "class": "DV_DATE_TIME",
                    "value": "2005-12-20T17:49:46.000+01:00"
                  }
                }
              ],
              "name": {
                "class": "DV_TEXT",
                "value": "Event Series"
              },
              "origin": {
                "class": "DV_DATE_TIME",
                "value": "2005-12-20T17:49:46.000+01:00"
              }
            },
            "encoding": {
              "class": "CODE_PHRASE",
              "code_string": "UTF-8",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "IANA_character-sets"
              }
            },
            "language": {
              "class": "CODE_PHRASE",
              "code_string": "en",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "ISO_639-1"
              }
            },
            "name": {
              "class": "DV_TEXT",
              "value": "Indirect oximetry"
            },
            "subject": {
              "class": "PARTY_SELF"
            }
          }
        ],
        "context": {
          "class": "EVENT_CONTEXT",
          "setting": {
            "class": "DV_CODED_TEXT",
            "defining_code": {
              "class": "CODE_PHRASE",
              "code_string": "238",
              "terminology_id": {
                "class": "TERMINOLOGY_ID",
                "value": "openehr"
              }
            },
            "value": "other care"
          },
          "start_time": {
            "class": "DV_DATE_TIME",
            "value": "2005-12-20T17:49:46.000+01:00"
          }
        },
        "language": {
          "class": "CODE_PHRASE",
          "code_string": "en",
          "terminology_id": {
            "class": "TERMINOLOGY_ID",
            "value": "ISO_639-1"
          }
        },
        "name": {
          "class": "DV_TEXT",
          "value": "Vital Signs"
        },
        "territory": {
          "class": "CODE_PHRASE",
          "code_string": "SI",
          "terminology_id": {
            "class": "TERMINOLOGY_ID",
            "value": "ISO_3166-1"
          }
        },
        "uid": {
          "class": "OBJECT_VERSION_ID",
          "value": "0060656d-87a9-448f-a110-6d179022dbff::MyMedEHR::4"
        }
      },
      "parentId": "7ee7b4c4-b25d-44c8-90b6-178c9644202b",
      "type": "composition"
    }
  }
]

We want to access to the information using the values of the property archetype_node_id, for example, to reach temperature value, the path will be something like that

c/content[openEHR-EHR-OBSERVATION.body_temperature.v1]/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude

The values in [] is the value of archetype_node_id

I found that in array I only can put numeric values, so I use array_position function and subqueries. It works but I feel that is very complicated and must be an easier way to do this. I really would thank very much any help. My query is like this:

SELECT 
b.fecha, 
b.data.items[ARRAY_POSITION(b.data.items[*].archetype_node_id, 'at0004')].`value`.magnitude AS temperatura 
FROM (
    SELECT    
	a.fecha,    
	a.data.events[ARRAY_POSITION(a.data.events[*].archetype_node_id, 'at0003')].data
    FROM (
		SELECT DISTINCT
		composition.content.content[ARRAY_POSITION(composition.content.content[*].archetype_details[*].archetype_id[*].`value`, 'openEHR-EHR-OBSERVATION.body_temperature.v1')].data,
		FROM `ehr` AS composition
		INNER JOIN `ehr` AS ehr ON KEYS composition.parentId
		WHERE composition.type = 'composition'
		AND ANY archetype_details WITHIN composition.content SATISFIES archetype_details.archetype_id.`value` = 'openEHR-EHR-OBSERVATION.body_temperature.v1' END
    ) AS a
    WHERE a.data.archetype_node_id = 'at0002'
) AS b 
WHERE b.data.archetype_node_id = 'at0001'
2

To access dynamic fields:

parent_object.[ field_expr ]

field_expr can be any expression that evaluates to a string.

There are also other things you can look into: WITHIN, OBJECT functions.

3

Thank you for the fast reply.

The field_expr you mention represents the key of the object, am i correct?. In this case, I have no keys for the objects.

4

No, field_expr can be any expression. It will be evaluated and used as the field name.

So, field_expr evaluates to ‘at0002’, then

parent_object.at0002

is the same as

parent_object.[ field_expr ]

Let me look at your question some more…

5

Your example tells me we might need simpler support for paths. Here is our current approach.

SELECT (
    FIRST i FOR i IN (
        FIRST e FOR e IN (
            FIRST c FOR c IN composition.content.content
            WHEN c.archetype_node_id = "openEHR-EHR-OBSERVATION.body_temperature.v1"
                AND c.data.archetype_node_id = "at0002"
            END
        ).data.events
        WHEN e.archetype_node_id = "at0003"
            AND e.data.archetype_node_id = "at0001"
        END
    ).data.items
    WHEN i.archetype_node_id = "at0004"
    END
).`value`.magnitude
FROM ehr AS composition;
6

Thank you very much for your time and effort. It would be easier to accept something like

composition.content.content[archetype_node_id=“value”].data[archetype_node_id=“value”] and so on

7

Yes, we will look into that. It would not be that xpath syntax, but we will look into simplifying this. I looked into OpenEHR and AQL, looks interesting. Let us know if you run into challenges mapping to N1QL.