RedORM: playing with JSON

code-json.p6

use Red;
use Red::Type::Json;

model Bla {
    has $!id is serial;
    has Json $.num1 is rw is column;
}

my $*RED-DB = database "SQLite";

Bla.^create-table;
Bla.^create: :num1{:42bla} 

say Bla.^all.map({ .num1<bla> }).head;

Bla.^all.map({ .num1<ble> = 13 }).save;
say Bla.^all.map({ .num1<ble> }).head;

Bla.^all.map({ .num1<bla> -= .num1<ble> }).save;
say Bla.^all.map({ .num1<bla> }).head;

Bla.^all.map({ .num1<bli> = {:blo(3.14)} }).save;
say Bla.^all.map({ .num1<bli><blo> }).head;

Bla.^all.map({ .num1 = {:blu[1, 2, 3]} }).save;
say Bla.^all.map({ .num1<blu> }).head;

Bla.^all.map({ .num1<ble>:delete }).save;
say Bla.^all.map({ .num1<ble> }).head;

output.txt

42
13
29
3.14
[1 2 3]
(Json)

SQL.sql

SELECT
   JSON_EXTRACT(bla.num1, '$.bla') as "data_1"
FROM
   bla
LIMIT 1;

UPDATE bla SET
   num1 = JSON_SET(bla.num1, '$.ble', JSON('13'))
;

SELECT
   JSON_EXTRACT(bla.num1, '$.ble') as "data_1"
FROM
   bla
LIMIT 1;

UPDATE bla SET
   num1 = JSON_SET(bla.num1, '$.bla', JSON_EXTRACT(bla.num1, '$.bla') - JSON_EXTRACT(bla.num1, '$.ble'))
;

SELECT
   JSON_EXTRACT(bla.num1, '$.bla') as "data_1"
FROM
   bla
LIMIT 1;

UPDATE bla SET
   num1 = JSON_SET(bla.num1, '$.bli', JSON('{
     "blo": 3.14
   }'))
;

SELECT
   JSON_EXTRACT(bla.num1, '$.bli.blo') as "data_1"
FROM
   bla
LIMIT 1;

UPDATE bla SET
   num1 = JSON('{
     "blu": [
       1,
       2,
       3
     ]
   }')
;

SELECT
   JSON_EXTRACT(bla.num1, '$.blu') as "data_1"
FROM
   bla
LIMIT 1
;

UPDATE bla SET
   num1 = JSON_REMOVE(bla.num1, '$.ble')
;

SQL : SELECT
   JSON_EXTRACT(bla.num1, '$.ble') as "data_1"
FROM
   bla
LIMIT 1;