14.9.1.3 Querying JSON Relational Duality View

Query a JSON relational duality view to retrieve related data as JSON documents.

A JSON Relational Duality View has a single JSON-type column named DATA, so query the view like this:

-- Retrieve 3 action item JSON docs by _id value
select data
  from action_items_dv
 where json_value(data,'$._id') in (14, 25, 27)

Each row of data retrieved looks like the following:

{
  "_id": 14,
  "name": "End of Year Party",
  "status": "OPEN",
  "team": [
    {
      "team_assignment_id": 23,
      "role": "MEMBER",
      "staff_id": 4,
      "name": "David"
    },
    {
      "team_assignment_id": 24,
      "role": "LEAD",
      "staff_id": 5,
      "name": "Georgia"
    },
    {
      "team_assignment_id": 25,
      "role": "MEMBER",
      "staff_id": 13,
      "name": "Jane"
    }
  ],
  "_metadata": {
    "etag": "5891809CF5691D5400D4141A65123976",
    "asof": "000025C52A64CB47"
  }
}