JSON Applications

SELECT
    p.name        AS product_name,
    json_extract(p.specs, '$.cpu') AS cpu
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'laptop%'
  AND p.is_active = 1
  AND p.specs IS NOT NULL
ORDER BY p.name;

SELECT
    p.name                                     AS product_name,
    p.price,
    json_extract(p.specs, '$.screen_size')     AS screen_size,
    json_extract(p.specs, '$.cpu')             AS cpu,
    json_extract(p.specs, '$.ram_gb')          AS ram_gb,
    json_extract(p.specs, '$.storage_gb')      AS storage_gb,
    json_extract(p.specs, '$.battery_hours')   AS battery_hours
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'laptop%'
  AND p.is_active = 1
  AND p.specs IS NOT NULL
ORDER BY p.price DESC
LIMIT 20;

SELECT
    cat.name          AS category,
    COUNT(*)          AS no_specs_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.specs IS NULL
  AND p.is_active = 1
GROUP BY cat.name
ORDER BY no_specs_count DESC;

SELECT
    je.key,
    je.value,
    je.type
FROM products AS p,
     json_each(p.specs) AS je
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'laptop%'
  AND p.specs IS NOT NULL
  AND p.id = (
      SELECT MIN(p2.id) FROM products AS p2
      INNER JOIN categories AS c2 ON p2.category_id = c2.id
      WHERE c2.slug LIKE 'laptop%' AND p2.specs IS NOT NULL
  );

SELECT
    p.name       AS product_name,
    p.price,
    json_extract(p.specs, '$.ram_gb')      AS ram_gb,
    json_extract(p.specs, '$.storage_gb')  AS storage_gb,
    json_extract(p.specs, '$.cpu')         AS cpu
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'laptop%'
  AND p.is_active = 1
  AND p.specs IS NOT NULL
  AND json_extract(p.specs, '$.ram_gb') >= 32
  AND json_extract(p.specs, '$.storage_gb') >= 1024
ORDER BY p.price DESC;

SELECT
    json_extract(p.specs, '$.cpu')     AS cpu,
    COUNT(*)                           AS product_count,
    ROUND(AVG(p.price))                AS avg_price,
    MIN(p.price)                       AS min_price,
    MAX(p.price)                       AS max_price
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE (cat.slug LIKE 'laptop%' OR cat.slug LIKE 'desktop%')
  AND p.is_active = 1
  AND p.specs IS NOT NULL
GROUP BY json_extract(p.specs, '$.cpu')
HAVING COUNT(*) >= 3
ORDER BY avg_price DESC;

SELECT
    json_extract(p.specs, '$.panel')          AS panel_type,
    COUNT(*)                                   AS product_count,
    ROUND(AVG(p.price))                        AS avg_price,
    ROUND(AVG(json_extract(p.specs, '$.refresh_rate'))) AS avg_refresh_rate,
    SUM(CASE WHEN json_extract(p.specs, '$.resolution') = 'FHD' THEN 1 ELSE 0 END) AS fhd_count,
    SUM(CASE WHEN json_extract(p.specs, '$.resolution') = 'QHD' THEN 1 ELSE 0 END) AS qhd_count,
    SUM(CASE WHEN json_extract(p.specs, '$.resolution') = '4K'  THEN 1 ELSE 0 END) AS uhd_4k_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'monitor%'
  AND p.is_active = 1
  AND p.specs IS NOT NULL
GROUP BY json_extract(p.specs, '$.panel')
ORDER BY avg_price DESC;

SELECT
    p.name         AS product_name,
    p.specs        AS original_specs,
    json_set(p.specs, '$.warranty_years', 3) AS modified_specs
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'laptop%'
  AND p.specs IS NOT NULL
LIMIT 3;

SELECT
    p.name        AS product_name,
    p.specs       AS original_specs,
    json_remove(p.specs, '$.tdp_watts') AS specs_without_tdp
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.slug LIKE 'gpu%'
  AND p.specs IS NOT NULL
LIMIT 5;

WITH spec_keys AS (
    SELECT
        cat.name       AS category,
        je.key,
        COUNT(*)       AS key_count
    FROM products AS p
    INNER JOIN categories AS cat ON p.category_id = cat.id,
         json_each(p.specs) AS je
    WHERE p.specs IS NOT NULL
      AND p.is_active = 1
    GROUP BY cat.name, je.key
),
category_totals AS (
    SELECT
        cat.name       AS category,
        COUNT(*)       AS total_products
    FROM products AS p
    INNER JOIN categories AS cat ON p.category_id = cat.id
    WHERE p.specs IS NOT NULL
      AND p.is_active = 1
    GROUP BY cat.name
)
SELECT
    sk.category,
    sk.key               AS spec_key,
    sk.key_count,
    ct.total_products,
    ROUND(100.0 * sk.key_count / ct.total_products, 1) AS presence_pct
FROM spec_keys AS sk
INNER JOIN category_totals AS ct ON sk.category = ct.category
ORDER BY sk.category, sk.key_count DESC;