JSON Cells

A JSON column, or any text column that holds JSON, is read by the MySQL_GetJSON natives without a parser in Pawn. A cell is parsed the first time one of them reads it, and every next read of that cell uses what was parsed.

Keys and pointers

A key is the name of a member of the top object. A key that starts with a slash is a JSON Pointer, which reaches members of objects inside and elements of arrays, from 0.

Cell	Key	Value
{"amount": 3}	amount	3
{"position": [1.5, 2.0, 3.0]}	/position/2	3.0
{"owner": {"name": "Carl"}}	/owner/name	"Carl"
[10, 20, 30]	/1	20

An empty key, where the native allows one, is the whole cell.

Reading values

Native	Gives
MySQL_GetJSONInt	An integer. A float is cut, a boolean is 0 or 1, and a string holding a number is read.
MySQL_GetJSONFloat	A float, from a number, a boolean or a string holding a number.
MySQL_GetJSONBool	A bool. A number is true when it is not 0.
MySQL_GetJSONString	The text of a string, or the JSON of any other value.

The number natives take a fallback, given when the value does not exist, is null, or is an object or an array. A value added in a later version of a script reads as its default from the rows saved before it.

new MySQL:gDatabase;

forward OnWeaponRead(playerid);

stock LoadWeapon(playerid, itemid) {
    MySQL_Query(gDatabase, "OnWeaponRead", playerid, "SELECT attributes FROM items WHERE id = %i", itemid);
}

public OnWeaponRead(playerid) {
    new const
        WEAPON:weapon = WEAPON:MySQL_GetJSONInt(0, "attributes", "weapon"),
        ammo = MySQL_GetJSONInt(0, "attributes", "ammo", 30)
    ;

    if (MySQL_GetJSONBool(0, "attributes", "broken")) {
        return SendClientMessage(playerid, -1, "This weapon is broken.");
    }

    GivePlayerWeapon(playerid, weapon, ammo);

    return 1;
}

Objects and arrays

MySQL_GetJSONType tells what a value is, as listed in JSON types. MySQL_GetJSONCount counts the members of an object or the elements of an array, and MySQL_GetJSONKey gives the name of a member by its position, so a script can walk an object whose keys it does not know.

stock ReadPosition(row, &Float:x, &Float:y, &Float:z) {
    if (MySQL_GetJSONType(row, "data", "/position") != MYSQL_JSON_ARRAY || MySQL_GetJSONCount(row, "data", "/position") != 3) {
        return 0;
    }

    x = MySQL_GetJSONFloat(row, "data", "/position/0");
    y = MySQL_GetJSONFloat(row, "data", "/position/1");
    z = MySQL_GetJSONFloat(row, "data", "/position/2");

    return 1;
}

Writing JSON

%j writes a string as a JSON string, between double quotes and with its escapes. JSON is built in a query of its own with it, then given whole to a statement with %Q, so no part of it is parsed as SQL:

new
    MySQL:gDatabase,
    Statement:gSaveAttributes
;

stock PrepareItems() {
    gSaveAttributes = MySQL_Prepare(gDatabase, "UPDATE items SET attributes = %Q WHERE id = %i");
}

stock SaveAttributes(itemid, const note[], amount) {
    new const
        Query:json = MySQL_CreateQuery(gDatabase, "{\"note\": %j, \"amount\": %i}", note, amount)
    ;

    MySQL_Execute(gSaveAttributes, "", 0, json, itemid);
    MySQL_DestroyQuery(json);
}

The values of a statement are copied when it is sent, so the query that held the JSON can be destroyed at once. JSON functions of the server, such as JSON_SET, take their values with %q as any other string.

Text

With the latin1 character set, the text of the game, the component converts keys and strings between the code page of the game and the UTF-8 of JSON. A letter the database holds escaped, as \u00e7, reaches the script as the letter itself.