Example 5-20 Obtain the SCN Recorded When a Document Was Fetched

This example fetches from the race duality view, race_dv, a serialized representation of the race document identified by _id value 201.^Foot 1 The SCN is the value of field asof, which is in the object that is the value of field _metadata. It records the moment when the document is fetched.

SELECT json_serialize(DATA PRETTY) FROM race_dv rdv
  WHERE rdv.DATA."_id" = 201;

Result:

JSON_SERIALIZE(DATAPRETTY)
--------------------------
{"_id"       : 201,
 "_metadata" :
  {
    "etag" : "F6906A8F7A131C127FAEF32CA43AF97A",
    "asof" : "00000000000C4175"
  },
 "name"      : "Blue Air Bahrain Grand Prix",
 "laps"      : 57,
 "date"      : "2022-03-20T00:00:00",
 "podium"    : {...},
 "result"    : [ {...} ]
}

1 row selected.

Example 5-21 Retrieve a Race Document As Of the Moment Another Race Document Was Retrieved

This example fetches the race document identified by raceId value 203 in the state that corresponds to the SCN of race document 201 (see Example 5-20).

SELECT json_serialize(DATA PRETTY) FROM race_dv
  AS OF SCN to_number('00000000000C4175', 'XXXXXXXXXXXXXXXX')
  WHERE json_value(DATA, '$._id') = 203;

Result:

JSON_SERIALIZE(DATAPRETTY)
--------------------------
{"_id"       : 203,
 "_metadata" :
  {
    "etag" : "EA6E1194C012970CA07116EE1EF167E8",
    "asof" : "00000000000C4175"
  },
  
 "name"      : "Australian Grand Prix",
 "laps"      : 58,
 "date"      : "2022-04-09T00:00:00",
 "podium"    : {...},
 "result"    : [ {...} ]
}

1 row selected.