Best way to query JSON string on a table

[Gajendra Singh1]

I want to query a JSON string (let's call it smaller_json) on a table column containing JSON string data (lets call it bigger_json). The 'smaller_json' can either be an exact match or a subset of the 'bigger_json'.  The table has around 300 records, meaning there are 300 JSON entries that need to be queried. What is the most efficient way to perform this query?

var bigger_json = {

"lab": "testlab",

"testbed": "hotpool",

"subtestbed": "",

"type": "instance",

"mode": "dedicated",

"genre": "subprod",

"flavour": "shard,adcv1",

"size": "pico",

"setup": true

};

var smaller_json = {

"subtestbed": "",

"type": "instance",

"mode": "dedicated",

"genre": "subprod",

"flavour": "shard,adcv1",

"size": "pico"

};

[pugazhenthi]

I think you may have done this already, but for future reference, if anyone has the same use case, they can look into the approach below.

While storing the JSON values, make sure it's completely flattened (non-pretty) JSON format, and when you do search, make sure your search string is also flattened (non-pretty) JSON format.

Use the CONTAINS query to check if the sub-query is present in the parent JSON or not.

[Maik Skoddow]

I'm sorry so say this, but your proposed approach is not helpful and will not lead to any positive result. Due to the complex nature of JSON-based object data a pure approach on a syntactical level is not sufficient. Instead you need to perform a semantical test which cannot be performed with a database query. You have to iterate all records and perform some code.

What really surprises me is that you both work in an “AI first” company, but don't ask the AI. So I did (Google AI studio) and got the following code generated:

/**
 * Checks if a 'subObject' is a subset of 'superObject'.
 * - For objects: all keys in subObject must exist in superObject, and their values must match (recursively).
 * - For arrays: every element in subObject must find a matching element in superObject (recursively).
 *   Order in arrays doesn't strictly matter for this "subset" check, nor does superObject having extra elements.
 * - For primitives: they must be strictly equal.
 *
 * @Param {*} subObject The potential subset (parsed JSON).
 * @Param {*} superObject The potential superset (parsed JSON).
 * @returns {boolean} True if subObject is a subset of superObject, false otherwise.
 */
function isSubset(subObject, superObject) {
    // 1. Handle primitives (and null)
    if (typeof subObject !== 'object' || subObject === null) {
        return subObject === superObject;
    }

    // At this point, subObject is an object or array

    // 2. Type mismatch (e.g., subObject is array, superObject is object)
    // Also handles if superObject is null or not an object when subObject is.
    if (typeof superObject !== 'object' || superObject === null || Array.isArray(subObject) !== Array.isArray(superObject)) {
        return false;
    }

    if (Array.isArray(subObject)) {
        // For subObject (array) to be part of superObject (array):
        // Every element in 'subObject' must find a corresponding element in 'superObject'
        // for which isSubset returns true.
        // This means subObject can't be longer than superObject if it's not empty.
        if (subObject.length === 0) return true; // Empty array is a subset of any array

        return subObject.every(subItem =>
            superObject.some(superItem => isSubset(subItem, superItem))
        );
    } else { // It's an object
        for (const key in subObject) {
            if (subObject.hasOwnProperty(key)) {
                if (!superObject.hasOwnProperty(key)) {
                    return false; // Key missing in superObject
                }
                if (!isSubset(subObject[key], superObject[key])) {
                    return false; // Values for the key don't match
                }
            }
        }
        return true; // All keys and their values in subObject are found and match in superObject
    }
}

/**
 * Wrapper function to parse JSON strings and then call isSubset.
 * @Param {string} smallJsonString
 * @Param {string} largeJsonString
 * @returns {boolean}
 */
function isJsonPayloadSubset(smallJsonString, largeJsonString) {
    try {
        const smallPayload = JSON.parse(smallJsonString);
        const largePayload = JSON.parse(largeJsonString);
        return isSubset(smallPayload, largePayload);
    } catch (error) {
        console.error("Error parsing JSON:", error);
        return false; // Or re-throw, depending on how you want to handle parse errors
    }
}

// --- Examples ---

// Example 1: Simple Object
const largeObj1 = '{"name": "Alice", "age": 30, "city": "New York"}';
const smallObj1_match = '{"age": 30, "city": "New York"}';
const smallObj1_mismatch_value = '{"age": 31}';
const smallObj1_mismatch_key = '{"occupation": "Engineer"}';

console.log("Example 1.1 (Match):", isJsonPayloadSubset(smallObj1_match, largeObj1)); // true
console.log("Example 1.2 (Mismatch Value):", isJsonPayloadSubset(smallObj1_mismatch_value, largeObj1)); // false
console.log("Example 1.3 (Mismatch Key):", isJsonPayloadSubset(smallObj1_mismatch_key, largeObj1)); // false

// Example 2: Nested Objects
const largeObj2 = '{"user": {"id": 123, "name": "Bob"}, "status": "active"}';
const smallObj2_match = '{"user": {"name": "Bob"}}';
const smallObj2_mismatch = '{"user": {"id": 456}}';

console.log("Example 2.1 (Match):", isJsonPayloadSubset(smallObj2_match, largeObj2)); // true
console.log("Example 2.2 (Mismatch):", isJsonPayloadSubset(smallObj2_mismatch, largeObj2)); // false

// Example 3: Arrays
const largeArr1 = '[{"id": 1, "val": "A"}, {"id": 2, "val": "B"}, {"id": 3, "val": "C"}]';
const smallArr1_match1 = '[{"id": 2, "val": "B"}]'; // Single element match
const smallArr1_match2 = '[{"val": "C"}, {"id": 1}]'; // Multiple elements, different order, partial match
const smallArr1_mismatch = '[{"id": 4}]';
const emptySmallArr = '[]';

console.log("Example 3.1 (Match 1):", isJsonPayloadSubset(smallArr1_match1, largeArr1)); // true
console.log("Example 3.2 (Match 2):", isJsonPayloadSubset(smallArr1_match2, largeArr1)); // true
console.log("Example 3.3 (Mismatch):", isJsonPayloadSubset(smallArr1_mismatch, largeArr1)); // false
console.log("Example 3.4 (Empty Small Array):", isJsonPayloadSubset(emptySmallArr, largeArr1)); // true

// Example 4: Mixed Types
const largeMixed = '{"data": [10, {"key": "value"}, 30], "flag": true}';
const smallMixed_match = '{"data": [{"key": "value"}]}';
const smallMixed_mismatch_type = '{"data": {"key": "value"}}'; // small.data is object, large.data is array

console.log("Example 4.1 (Match):", isJsonPayloadSubset(smallMixed_match, largeMixed)); // true
console.log("Example 4.2 (Mismatch Type):", isJsonPayloadSubset(smallMixed_mismatch_type, largeMixed)); // false

// Example 5: Primitive as the small payload
const largePrimitiveHost = '"hello"'; // Note: JSON string must be a valid JSON value
const smallPrimitive1 = '"hello"';
const smallPrimitive2 = '"world"';
console.log("Example 5.1 (Primitive Match):", isJsonPayloadSubset(smallPrimitive1, largePrimitiveHost)); // true
console.log("Example 5.2 (Primitive Mismatch):", isJsonPayloadSubset(smallPrimitive2, largePrimitiveHost)); // false

const largeNumberHost = '123';
const smallNumber1 = '123';
const smallNumber2 = '456';
console.log("Example 5.3 (Primitive Number Match):", isJsonPayloadSubset(smallNumber1, largeNumberHost)); // true
console.log("Example 5.4 (Primitive Number Mismatch):", isJsonPayloadSubset(smallNumber2, largeNumberHost)); // false


// Example 6: Small object is empty
const largeObj6 = '{"a": 1}';
const smallObj6_empty = '{}';
console.log("Example 6.1 (Empty small object):", isJsonPayloadSubset(smallObj6_empty, largeObj6)); // true

// Example 7: Small array contains object, large array doesn't have one that matches deeply enough
const largeArr7 = '[{"id": 1, "val": "A", "extra": "foo"}, {"id": 2, "val": "B"}]';
const smallArr7_match = '[{"id": 1, "val": "A"}]'; // This item from small is subset of first item in large
const smallArr7_mismatch = '[{"id": 1, "val": "X"}]'; // No item in large matches this
console.log("Example 7.1 (Array item match):", isJsonPayloadSubset(smallArr7_match, largeArr7)); // true
console.log("Example 7.2 (Array item mismatch):", isJsonPayloadSubset(smallArr7_mismatch, largeArr7)); // false

// Example 8: Small payload expects an array where large payload has an object
const largeObj8 = '{"items": {"a": 1, "b": 2}}';
const smallArr8_type_mismatch = '{"items": [1, 2]}';
console.log("Example 8.1 (Type mismatch array vs object):", isJsonPayloadSubset(smallArr8_type_mismatch, largeObj8)); // false