Power of SQL for JSON and String Arrays (2/2)

Photo by Caspar Camille Rubin on Unsplash

In our previous post, we explored basic SQL functions for handling JSON and string arrays. Now, let’s dive into little complex scenarios that demonstrate the true power of these capabilities. These examples will push the boundaries of what you can achieve with SQL and semi-structured data.

Power of SQL for JSON and String Arrays (1/2)

Scenario 1: Nested JSON Structures and Path Traversal

Imagine your products table has a deeply nested JSON structure for specifications:

{
  "name": "Gaming PC",
  "specifications": {
    "cpu": {
      "brand": "AMD",
      "model": "Ryzen 9 5900X"
    },
    "gpu": {
      "brand": "Nvidia",
      "model": "RTX 3080"
    }
  }
}

To extract the CPU model, you’ll need to traverse the nested structure. Here’s how:

-- PostgreSQL
SELECT JSON_EXTRACT_PATH_TEXT(details, 'specifications', 'cpu', 'model') AS cpu_model
FROM products;

-- SQL Server
SELECT JSON_VALUE(details, '$.specifications.cpu.model') AS cpu_model
FROM products;