Skip to content

05-json-aggregations.md

Latest commit

 

History

History
115 lines (89 loc) · 2.02 KB

05-json-aggregations.md

File metadata and controls

115 lines (89 loc) · 2.02 KB

JSON Aggregations

Use JSON functions to analyze and aggregate JSON data.

JSON Length

Array/Object Size

use tommyknocker\pdodb\helpers\Db;

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

Order by Length

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

JSON Type

Get Value Type

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

// Returns: 'array', 'object', 'string', 'number', 'boolean', 'null'

JSON Keys

Get Object Keys

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

JSON Aggregation Examples

Count Tags

$tagStats = $db->find()
    ->from('users')
    ->select([
        'tag_count' => Db::jsonLength('tags'),
        'has_many_tags' => Db::jsonLength('tags') . ' > 5'
    ])
    ->get();

Analyze JSON Structure

$analysis = $db->find()
    ->from('users')
    ->select([
        'id',
        'meta_type' => Db::jsonType('meta'),
        'meta_keys_count' => Db::jsonLength(Db::jsonKeys('meta')),
        'tags_count' => Db::jsonLength('tags')
    ])
    ->get();

Group by JSON Properties

Count by JSON Value

$stats = $db->find()
    ->from('users')
    ->select([
        'city' => Db::jsonGet('meta', ['city']),
        'city_count' => Db::count()
    ])
    ->groupBy('city')
    ->orderBy('city_count', 'DESC')
    ->get();

Examples

Next Steps