Christmas Puzzle – The Solution – Learning is not a spectator sport

Christmas Puzzle – The Solution

December 26, 2024

1–2 minutes

A huge “Well done!” to all those people that made the realisation that we had to be storing the data in a datatype that can manipulate the data internally as it is stored. Thus datatypes like JSON and XMLTYPE became the obvious candidates.

No-one matched exactly the way I constructed the problem, but for those of you that tinkered around until you could then find the right combination of JSON fragment until the data matched the numbers I provided in my example – I call that a successful solution to the quiz!
Give yourself a pat on the back!

So what was my construction? Yes it was JSON but another clue with the column name of DATA. I used a new type of table in 23ai



SQL> create json collection table t;

Table created.

One of interesting things about this table is that if you provide an element as a unique identifier, we will go ahead and use it.



SQL> insert into t values ('{"_id":123123,"Merry":"Christmas"}');

1 row created.

SQL> select data from t;

DATA
---------------------------------------
{"_id":123123,"Merry":"Christmas"}

But if you do not, then we will automatically add one to the JSON



SQL> insert into t values ('{"Merry":"Xmas!!"}');

1 row created.

SQL> select data from t;

DATA
-------------------------------------------------------
{"_id":"676cd9d6b9ed3fcde32de8f3","Merry":"Xmas!!"}

And that is hence a very easy way to insert less data and end up with a longer length result as per what you saw in the puzzle.


SQL> select length(data), data from t;

LENGTH(DATA) DATA
------------ --------------------------------------------------------
          52 {"_id":123123,"Merry":"Christmas"}
          58 {"_id":"676cd9d6b9ed3fcde32de8f3","Merry":"Xmas!!"}

Happy New Year!

3 responses to “Christmas Puzzle – The Solution”

iudithd5bf8e4d8d

December 26, 2024 at 9:44 pm

Hi Connor,

Well … just for my full understanding, can you please explain how are the length values calculated, to get 52 and 58 ?

If we count the characters of the string representation of DATA, as we see it displayed, we get 34 and 51.

Another “witchery” going one here ?!?

Thanks a lot in advance 🙂

Cheers & Happy New Year 🙂
Iudith

Reply
1. Connor McDonald
  
  January 3, 2025 at 7:25 am
  
  check out this post https://medium.com/db-one/jsonb-bson-oson-a-deep-dive-into-binary-json-formats-d49e41bdb314
  
  Reply
iudithd5bf8e4d8d

January 3, 2025 at 10:39 pm

Hi Connor,

Thanks a lot for your answer, and, once again, Happy New Year 2025 🙂

Yes, I do remember a super-technical Asktom Office Hours session of Beda & Luc ( rather a dialogue between the two … )
on the new storage optimizations for JSON …

I guess that these optimizations were designed for the general case, but, in some particular cases,
like in your tricky sample, they may behave just the opposite:

select data, length(data), length(json_serialize(data)), vsize(data)
from t
/

DATA LENGTH(DATA) LENGTH(JSON_SERIALIZE(DATA)) VSIZE(DATA)
————————————————————————————————————————————–{“_id”:123123,”Merry”:”Christmas”} 52 34 82
{“Merry”:”Xmas!!”,”_id”:”6777f2b0aef586a9bcf027fa”} 58 51 88

So, we see several “size variants” here … Go figure …

Cheers & Best Regards,
Iudith
( I apologize that I could not figure out how to choose a fixed font size for my comment 😦 … )

Reply

Learning is not a spectator sport