Creating chunks

SQL++
1

Is there a method to split a sting into fixed size chunks except the last chunk?

I have a document that looks like:
{
“name”: “some name”,
“body”: “base64 encoded file”
}

I need to split this document into documents like:
{
“sequence”: 0,
“name”: “some name”,
“body”: “chunk 1 of base64 encoded file”
},
{
“sequence”: 1,
“name”: “some name”,
“body”: “chunk 2 of base64 encoded file”
},…

etcetera until the entire file is split into chunks.

2 
WITH s AS ("abcdefghijklmno"),
     step AS (2) 
 SELECT ARRAY {"sequence":pos, "name":"xyz", "body": SUBSTR(s,v,step)} 
        FOR pos:v IN ARRAY_RANGE(0,LENGTH(s),step) 
        END AS obj;
{
    "requestID": "b31be80c-40f8-4873-8a27-2095c38679fd",
    "signature": {
        "obj": "array"
    },
    "results": [
    {
        "obj": [
            {
                "body": "ab",
                "name": "xyz",
                "sequence": 0
            },
            {
                "body": "cd",
                "name": "xyz",
                "sequence": 1
            },
            {
                "body": "ef",
                "name": "xyz",
                "sequence": 2
            },
            {
                "body": "gh",
                "name": "xyz",
                "sequence": 3
            },
            {
                "body": "ij",
                "name": "xyz",
                "sequence": 4
            },
            {
                "body": "kl",
                "name": "xyz",
                "sequence": 5
            },
            {
                "body": "mn",
                "name": "xyz",
                "sequence": 6
            },
            {
                "body": "o",
                "name": "xyz",
                "sequence": 7
            }
        ]
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.31174ms",
        "executionTime": "1.258403ms",
        "resultCount": 1,
        "resultSize": 992,
        "serviceLoad": 2
    }
}
3

Thanks for our quick reply. I tried to extend the query with a document as input and somehow I never get a result. Below you will find one of my tests. Do you know how to get your example working with a select in the with / what am I doing wrong?

WITH
s AS (
SELECT raw name
FROM travel-sample.inventory.hotel limit 1
),
step as (2)
SELECT ARRAY {“sequence”:pos, “name”:“xyz”, “body”: SUBSTR(s,v,step)}
FOR pos:v IN ARRAY_RANGE(0,LENGTH(s),step)
END AS obj;

4

Of course you’d need to quote travel-sample else you’d be getting a syntax error (but I assume this isn’t the case) and the results are an array so you’d have to dereference it:

cbq> WITH s AS ((SELECT raw name FROM `travel-sample`.inventory.hotel limit 1)[0]), step as (2) SELECT ARRAY {"sequence":pos, "name":"xyz", "body": SUBSTR(s,v,step)} FOR pos:v IN ARRAY_RANGE(0,LENGTH(s),step) END AS obj;
{
    "requestID": "f74f3f14-5f71-47b5-a4a1-871d0bfe000c",
    "signature": {
        "obj": "array"
    },
    "results": [
    {
        "obj": [
            {
                "body": "Me",
                "name": "xyz",
                "sequence": 0
            },
            {
...

Or you could fetch it element by element rather than using it as the array (from clause):

cbq> WITH s AS (SELECT raw name FROM `travel-sample`.inventory.hotel limit 1), step as (2) SELECT ARRAY {"sequence":pos, "name":"xyz", "body": SUBSTR(s,v,step)} FOR pos:v IN ARRAY_RANGE(0,LENGTH(s),step) END AS obj FROM s;
{
    "requestID": "fc11b227-2ad5-4eb7-aaca-8df0c3da35c5",
    "signature": {
        "obj": "array"
    },
    "results": [
    {
        "obj": [
            {
                "body": "Me",
                "name": "xyz",
                "sequence": 0
            },
            {
                "body": "dw",
                "name": "xyz",
                "sequence": 1
            },
...

HTH.

1 Like
5

Want this on documents from bucket

WITH step AS (2) 
SELECT ARRAY {"sequence":pos, "name":"xyz", "body": SUBSTR(h.name , v, step)} 
        FOR pos:v IN ARRAY_RANGE(0,LENGTH(h.name),step) 
        END AS obj
FROM  `travel-sample`.inventory.hotel  AS h
LIMIT 1;