2022

Buoyed by his early success Austin sets out for some thorough function testing. He eventually comes across this result. SELECT JSON_VALUE(everything_else,’$.Name’) nm,   sites(JSON_QUERY(everything_else,’$.MRNS’)) sites FROM patient_data WHERE JSON_VALUE(everything_else,’$.Name’) = ‘Cooter’; NM SITES ———- ————————————————– Cooter [“Hospital”,”Hospital”] The underlying JSON is this: {“Name”:”Cooter”, “EMPI”:653998, “MRNS”:[{“Site”:”Hospital”,”MRN”:8753}, {“Site”:”Hospital”,”MRN”:44428}]} This patient has two MRN’s from Hospital but the … Read more

After several decades working with Relational Data world-renowned developer Austin Tatious has started a new position working with JSON. He’s working for a patient patient management software company. Being a self starter he dives right in to check what tables exist in the database. SELECT table_name FROM user_tables; TABLE_NAME —————————— PATIENT_DATA Only one table? He … Read more

Not content to just put EMPI keys and values in disparate places in the JSON some applications started entering EMPI as objects. INSERT INTO json_patient VALUES(‘{“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}}’); My table function tried its best… SELECT * FROM TABLE(add_empi) WHERE empi = ‘$.EMPI’; PATIENTDATA EMPI ———————————————- ——————————— {“pname”:”Rosco Coltrane1″,”EMPI”:1} $.EMPI {“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}} $.EMPI It did indeed find EMPI but … Read more

My first JSON DB project did not go well because the data structure (or lack thereof) quickly overwhelmed the applications ability to process it. If I had it to do over again I would have put more of the application in the database to simplify and manage access. That experience inspires this series of posts … Read more

Another option for synchronizing JSON Search Indexes is Manual which is enabled by altering the meta-data of the index like this: ALTER INDEX jindex PARAMETERS (‘replace metadata sync (manual)’); Now when DML occurs… INSERT INTO jtable VALUES(‘{“JTOP”:3}’); SELECT * FROM jtable WHERE JSON_EXISTS(jcol,’$.JTOP’); JCOL ——————- {“JTOP”:1} {“JTOP”:2} It will not show up in queries (even … Read more

I was reading this article by Roger Ford and one statement caught my eye “You may notice that if you insert a document but do not commit it, some searches using a search index will not find it, because the index has not yet been synchronized.” I quickly ran a test for this situation. I … Read more