SQLite doesn’t have native support for arrays. I would think that my method (thinking outlined below) of making a custom BLOB encoding would be a fairly common workaround (yes, I need an array rather than normalizing the table). The benefit of representing an integer array as a BLOB is primarily the space savings, for example:

13,24,455,23,64789

stored as TEXT will take up 18 bytes (commas included, making assumptions here)

but if one were to store the above TEXT in a custom encoded BLOB format it would look like this:

0x000D001801C7FD15

Where every number is assumed to take up 2 Bytes (0 to 65,535 or 0000 to FFFF). This BLOB, to my understanding, would then be 10 Bytes nearly half as small as storing it as TEXT. This would also magnify with the number of rows

My question then is this:

Is there a good way of unnesting a BLOB by width? Say that I want to unnest the BLOB so that each row represents an integer. Can I take the above BLOB and turn it into this?

CodePudding user response：

SQLite's HEX() function

interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob.

After you get the blob as a string use SUBSTR() to slice it in 4 char parts:

WITH cte(id) AS (VALUES (1), (2), (3), (4), (5))
SELECT HEX(t.col) hex_col, 
       c.id,
       SUBSTR(HEX(t.col), (c.id - 1) * 4   1, 4) number
FROM tablename t JOIN cte c
ORDER BY t.rowid, c.id;

Or, with a recursive CTE to generate dynamically the list of values 1..n:

WITH cte(id) AS (
  SELECT 1 
  UNION ALL
  SELECT id   1
  FROM cte
  WHERE id < 5
)
SELECT HEX(t.col) hex_col, 
       c.id,
       SUBSTR(HEX(t.col), (c.id - 1) * 4   1, 4) number
FROM tablename t JOIN cte c
ORDER BY t.rowid, c.id;

Change col to the name of your blob column.

See the demo.