Skip to content

Latest commit

 

History

History
221 lines (171 loc) · 3.68 KB

json-querying.md

File metadata and controls

221 lines (171 loc) · 3.68 KB

JSON Querying

Query and extract JSON data using PDOdb's JSON API.

Extract JSON Values

jsonGet() - Extract by Path

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'city' => Db::jsonGet('meta', ['city']),
        'age' => Db::jsonGet('meta', ['age'])
    ])
    ->get();

Select JSON Columns

$products = $db->find()
    ->from('products')
    ->select([
        'id',
        'name',
        'specs' => 'specs'
    ])
    ->get();

foreach ($products as $product) {
    $specs = json_decode($product['specs'], true);
    // Process parsed JSON
}

Filter by JSON Values

JSON Path Comparison

// Find users older than 25 (from JSON metadata)
$adults = $db->find()
    ->from('users')
    ->where(Db::jsonPath('meta', ['age'], '>', 25))
    ->get();

Multiple JSON Conditions

$active = $db->find()
    ->from('users')
    ->where(Db::jsonPath('meta', ['age'], '>', 25))
    ->andWhere(Db::jsonContains('tags', 'php'))
    ->andWhere(Db::jsonExists('meta', ['verified']))
    ->get();

Check JSON Contains

Single Value

// Find users with 'php' in tags array
$phpDevs = $db->find()
    ->from('users')
    ->where(Db::jsonContains('tags', 'php'))
    ->get();

Multiple Values

// Find users with both 'php' and 'mysql' in tags
$fullStack = $db->find()
    ->from('users')
    ->where(Db::jsonContains('tags', ['php', 'mysql']))
    ->get();

Specific Path

// Check if JSON object has specific key
$withCity = $db->find()
    ->from('users')
    ->where(Db::jsonContains('meta', 'city', ['location']))
    ->get();

Order by JSON Values

Sort by JSON Field

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->orderBy(Db::jsonGet('meta', ['age']), 'DESC')
    ->get();

Sort by Array Length

$users = $db->find()
    ->from('users')
    ->orderBy(Db::jsonLength('tags'), 'DESC')
    ->get();

JSON Functions

Get Length

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'tag_count' => Db::jsonLength('tags')
    ])
    ->get();

Get Keys

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'meta_keys' => Db::jsonKeys('meta')
    ])
    ->get();

Get Type

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'tags_type' => Db::jsonType('tags')
    ])
    ->get();

Complex Examples

Search in Nested JSON

$users = $db->find()
    ->from('users')
    ->where(Db::jsonPath('profile', ['address', 'city'], '=', 'NYC'))
    ->get();

// JSON structure:
// { profile: { address: { city: "NYC" } } }

Filter by Array Contains All

// Users who have ALL of these skills
$experts = $db->find()
    ->from('users')
    ->where(Db::jsonContains('skills', ['php', 'mysql', 'docker']))
    ->get();

Order by JSON Array Length

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'skill_count' => Db::jsonLength('skills')
    ])
    ->orderBy('skill_count', 'DESC')
    ->get();

Database Differences

Generated SQL

MySQL:

SELECT JSON_EXTRACT(meta, '$.city') as city FROM users

PostgreSQL:

SELECT meta->>'city' as city FROM users

SQLite:

SELECT json_extract(meta, '$.city') as city FROM users

PDOdb handles this automatically.

Next Steps