[Bacardi]

Here is JSON example: https://tirkarthi.github.io/programm...rovements.html

PHP Code:

/*
./sqlite3
SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table user(id integer primary key, name text, interests json);
sqlite> insert into user values(null, "John", '{"likes": ["skating", "reading", "swimming"], "dislikes": ["cooking"]}');
sqlite> insert into user values(null, "Kate", '{"likes": ["reading", "swimming"], "dislikes": ["skating"]}');
sqlite> insert into user values(null, "Jim", '{"likes": ["reading", "swimming"], "dislikes": ["cooking"]}');
sqlite> .mode column
sqlite> select * from user;
id  name  interests                                                   
--  ----  ------------------------------------------------------------
1   John  {"likes": ["skating", "reading", "swimming"], "dislikes": ["cooking"]}                                               
2   Kate  {"likes": ["reading", "swimming"], "dislikes": ["skating"]}
3   Jim   {"likes": ["reading", "swimming"], "dislikes": ["cooking"]}

*/


public void OnPluginStart()
{
    RegConsoleCmd("sm_test", test);
}

Database MyDB;

public Action test(int client, int args)
{
    Database.Connect(connectcallback, "my_database", 0);

    return Plugin_Handled;
}

public void connectcallback(Database db, const char[] error, any data)
{
    if(MyDB != db)
        delete MyDB;

    MyDB = db;

    MyDB.Query(querytablecallback,
                        "CREATE TABLE IF NOT EXISTS user(    \
                        id INTEGER PRIMARY KEY,                \
                        name TEXT,                            \
                        interests JSON                        \
                        );",
                        0, DBPrio_Low);
}

public void querytablecallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;

    MyDB.Query(queryinsertcallback,
                        "INSERT INTO user VALUES(    \
                        null, \"John\", '{\"likes\": [\"skating\",\"reading\",\"swimming\"], \"dislikes\": [\"cooking\"]}\'    \
                        ),                            \
                        (                            \
                        null, \"Kate\", '{\"likes\": [\"reading\",\"swimming\"], \"dislikes\": [\"skating\"]}\'                \
                        ),                            \
                        (                            \
                        null, \"Jim\", '{\"likes\": [\"reading\",\"swimming\"], \"dislikes\": [\"cooking\"]}\'                \
                        );",
                        0, DBPrio_Low);
}



/*
select id, name, interests from user 
where interests->'$.likes'->>'$[0]' = 'reading';
*/
public void queryinsertcallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;

    MyDB.Query(queryvaluecallback,
                        "SELECT id, name, interests FROM user            \
                        WHERE interests->'$.likes'->>'$[0]' = 'reading';",
                        0, DBPrio_Low);
}

public void queryvaluecallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;


    // table exist, look results and print those

    if(results.AffectedRows > 0)
    {
        char buffer[256];

        while(results.FetchRow())
        {
            for(int x = 0; x < results.FieldCount; x++)
            {
                results.FetchString(x, buffer, sizeof(buffer));
                PrintToServer("%s", buffer);
            }
        }
    }

} 


output

sm_test
2
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
3
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
5
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
6
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
8
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
9
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
11
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
12
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
14
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
15
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
17
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
18
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}
20
Kate
{"likes": ["reading","swimming"], "dislikes": ["skating"]}
21
Jim
{"likes": ["reading","swimming"], "dislikes": ["cooking"]}