Oracle REST json db, do not see the record but can patch it

Question

I am testing Oracle Autonomous Json Database REST using Soda for access via c# (Unity3d) and all works good.

The process I use is:

• Create a json document with a time stamp, works
• Update the time-stamp regular, works

However, when I create a new record I cannot see it via OCI (JSON) but I can read and update that record. It takes quite a long time until I can see it, maybe it is cached.

I wrote some code to list all current records in this json collection:

> PlayerId: Jtm7EBceqzoSJLSfX6v37WOLHUyn
> PlayerName: PeterX
> StartTime: 10/29/2024 5:09:20 PM
---------------------------------
> PlayerId: Jtm7EBceqzoSJLSfX6v37WOLHUyn_
> PlayerName: ANONYMOUS NEW
> StartTime: 10/28/2024 8:15:48 PM
---------------------------------
> PlayerId: aaaaaaaaaa
> PlayerName: ANONYMOUS#1
> StartTime: 10/25/2024 4:22:55 PM
---------------------------------
PlayerId: bbbbbbbbbb
PlayerName: ANONYMOUS#2
StartTime: 10/29/2024 7:16:26 PM
---------------------------------
> PlayerId: ABCD
> PlayerName: MaxPax
> StartTime: 10/29/2024 9:49:36 PM
---------------------------------
PlayerId: 1234
PlayerName: MaxPax2
StartTime: 10/29/2024 7:20:31 PM
---------------------------------
PlayerId: 
PlayerName: 
StartTime: 10/29/2024 9:38:11 PM
---------------------------------
> PlayerId: ZÅÄÖ
> PlayerName: Linnéa
> StartTime: 10/29/2024 10:06:34 PM
---------------------------------
PlayerId: MmMmMmMm
PlayerName: UKRAINA
StartTime: 10/29/2024 10:42:21 PM

I can only see the records marked with ">".

I just refreshed OCI approx. one hour after I created records but it is still not updated.

And now, many hours later, the missing records have appeared so I can see them in OCI. I guess this is some delays in OCI.

Can someone please explain why this is happening?

script

The "°" is a bit strange as of now but I will implement Tuple in my binding code in Unity Cloud Code before going further.

using Unity.Services.CloudCode.Core;
using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Text.Json;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace PlayerActive;

public class MyModule
{
private static readonly HttpClient client = new HttpClient();
// URL to the Oracle SODA database
private const string url = "https://aaaaaaa-xxxx-1.oraclecloudapps.com/ords/xxxx/soda/latest/PlayerActive";

[CloudCodeFunction("PlayerActive")]
public async Task<string> PlayerActive(string token, string playerId, string playerName)
{
    var _idResponse = await GetPlayerDbJsonId(token, playerId);

    if (_idResponse.Contains("200°")) //  200°671bc5e00add524f797af11b
    {
        string result = _idResponse.Replace("200°", "");
        var _patchReturn = await PatchPlayerRecord(result, token);
        return _patchReturn;
    }
    else if (_idResponse == "404")
    {
        var myPayload = new PlayerValue
        {
            playerId = playerId,
            playerName = playerName,
            startTime = DateTime.UtcNow.ToLocalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ"),
            isOnline = true
        };
        
        string payload = JsonConvert.SerializeObject(myPayload);
        
        var _returnCreateRecord = await CreatePlayerActiveRecord(payload, token);
        return _returnCreateRecord;
    }
    else
    {
        return _idResponse;
    }
}

async Task<string> GetPlayerDbJsonId(string token, string playerId)
{
    // Set the Authorization header with Bearer token
    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
    
    try
    {
        // Create JSON query to search for playerId
        var queryJson = new JObject
        {
            ["playerId"] = playerId
        };

        // Prepare HTTP content for POST request
        StringContent content = new StringContent(queryJson.ToString(), Encoding.UTF8, "application/json");
        
        // Send POST request to search the database
        HttpResponseMessage response = await client.PostAsync(url + "?action=query", content);
        response.EnsureSuccessStatusCode();
        
        // Parse JSON response
        string responseBody = await response.Content.ReadAsStringAsync();
        JObject json = JObject.Parse(responseBody);
        JArray items = (JArray)json["items"];
        
        int _code = (int)response.StatusCode;

        if (items.Count > 0 && items[0]["value"] != null && items[0]["value"]["_id"] != null)
        {
            // Return only _Id from the first matching document’s value field
            return _code.ToString() + "°" + items[0]["value"]["_id"].ToString();
        }

        // Return null if no matching playerId is found
        return _code.ToString(); //"404";
    }
    catch (HttpRequestException e)
    {
        Console.WriteLine($"Request error: {e.Message}");
        return e.ToString();
    }
}

async Task<string> PatchPlayerRecord(string documentId, string token)
{
    string jsonPatch = $@"[
        {{
            ""op"": ""replace"",
            ""path"": ""/startTime"",
            ""value"": ""{DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ")}""
        }}
    ]";
    
    using (HttpClient client = new HttpClient())
    {
        // Set authorization header
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

        // Specify PATCH endpoint with the document key (documentId)
        string patchUrl = $"{url}/{documentId}";

        // Create HTTP content for the PATCH request
        StringContent content = new StringContent(jsonPatch, Encoding.UTF8, "application/json-patch+json");

        try
        {
            // Send PATCH request
            HttpResponseMessage response = await client.PatchAsync(patchUrl, content);

            // Check if the update succeeded
            if (response.IsSuccessStatusCode)
            {
                Console.WriteLine("Document updated successfully.");
                int _code = (int)response.StatusCode;
                return _code.ToString();
            }
            else
            {
                Console.WriteLine($"Failed to update document. Status: {response.StatusCode}");
                int _code = (int)response.StatusCode;
                return _code.ToString();
            }
        }
        catch (HttpRequestException e)
        {
            Console.WriteLine($"Request error: {e.Message}");
            return e.Message.ToString();
        }
    }
}

async Task<string> CreatePlayerActiveRecord(string _payload, string token)
{
    try
    {
        // Set the Authorization header with the provided access token
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

        // Create content with JSON payload and specify content type as application/json
        var content = new StringContent(_payload, Encoding.UTF8, "application/json");

        // Send POST request to the specified endpoint
        var urlResponse = await client.PostAsync(url, content);

        // Check if the response status indicates success
        if (urlResponse.IsSuccessStatusCode)
        {
            int _code = (int)urlResponse.StatusCode;
            return _code.ToString();
        }
        else
        {
            // Log error content if available
            var errorContent = urlResponse.Content != null ? await urlResponse.Content.ReadAsStringAsync() : "No content";
            Console.WriteLine($"Error: {urlResponse.StatusCode}, {errorContent}");
            int _code = (int)urlResponse.StatusCode;
            return _code.ToString();
        }
    }
    catch (HttpRequestException e)
    {
        // Log request-specific errors
        Console.WriteLine(e.Message);
        return e.Message; // Service Unavailable
    }
    catch (Exception ex)
    {
        // Log general exceptions
        Console.WriteLine(ex.ToString());
        return ex.ToString(); // Internal Server Error
    }
}
}

public class PlayerValue
{
    public string playerId { get; set; }
    public string playerName { get; set; }
    public string startTime { get; set; } // Changed to string for direct assignment
    public bool isOnline { get; set; }
}