📄 JSON & XML Operations

Meta & Apple Interview Deep Dive

🏢 Meta🏢 Apple⚡ Difficulty: Medium⏱️ 35 min

📋 Interview Question

ℹ️🔴 Meta/Apple Interview Question

"Given a users table with a JSONB profile_data column, write queries to: 1) Extract specific fields, 2) Filter by JSON properties, 3) Aggregate JSON arrays, 4) Update nested JSON values. Also demonstrate XML parsing for product catalogs."

Companies: Meta, Apple | Difficulty: Medium | Time: 35 minutes

📊 Setup: Users with JSON Data

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(200),
    profile_data JSONB,  -- Semi-structured profile information
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, email, profile_data) VALUES
('alice', 'alice@example.com', '{
    "name": "Alice Johnson",
    "age": 28,
    "address": {
        "city": "New York",
        "state": "NY",
        "zip": "10001"
    },
    "interests": ["hiking", "photography", "coding"],
    "social": {
        "twitter": "@alice",
        "linkedin": "alice-johnson"
    },
    "preferences": {
        "theme": "dark",
        "notifications": true,
        "language": "en"
    }
}'),
('bob', 'bob@example.com', '{
    "name": "Bob Smith",
    "age": 35,
    "address": {
        "city": "San Francisco",
        "state": "CA",
        "zip": "94102"
    },
    "interests": ["gaming", "cooking"],
    "social": {
        "twitter": "@bobsmith",
        "instagram": "bob.smith"
    },
    "preferences": {
        "theme": "light",
        "notifications": false,
        "language": "en"
    }
}'),
('charlie', 'charlie@example.com', '{
    "name": "Charlie Brown",
    "age": 22,
    "address": {
        "city": "Chicago",
        "state": "IL",
        "zip": "60601"
    },
    "interests": ["music", "travel", "photography", "reading"],
    "social": {
        "twitter": "@charlieb"
    },
    "preferences": {
        "theme": "auto",
        "notifications": true,
        "language": "es"
    }
}');

🔍 Part 1: Extracting JSON Fields

ℹ️🔍 JSON Operators

-> returns JSON object field by key
->> returns JSON object field as text
#> returns JSON object field by path (as JSON)
#>> returns JSON object field by path (as text)

-- Extract fields using -> and ->>
SELECT
    username,
    profile_data->>'name' AS name,
    profile_data->'address'->>'city' AS city,
    profile_data->'address'->>'state' AS state,
    (profile_data->>'age')::INT AS age
FROM users;

-- Using #> and #>> for nested paths
SELECT
    username,
    profile_data#>>'{address,city}' AS city,
    profile_data#>>'{social,twitter}' AS twitter
FROM users;

-- Extract array elements
SELECT
    username,
    profile_data->'interests'->0 AS first_interest,
    profile_data->'interests'->1 AS second_interest,
    jsonb_array_length(profile_data->'interests') AS interest_count
FROM users;

🔎 Part 2: Filtering by JSON Properties

-- Filter by exact value
SELECT username, profile_data->>'name' AS name
FROM users
WHERE profile_data->>'age' = '28';

-- Filter by numeric comparison
SELECT username, (profile_data->>'age')::INT AS age
FROM users
WHERE (profile_data->>'age')::INT > 25;

-- Filter by nested object property
SELECT username
FROM users
WHERE profile_data->'address'->>'state' = 'CA';

-- Filter by array contains
SELECT username, profile_data->>'name' AS name
FROM users
WHERE profile_data->'interests' @> '"photography"';

-- Using @> for containment
SELECT username
FROM users
WHERE profile_data @> '{"preferences": {"notifications": true}}';

-- Using ? for key existence
SELECT username
FROM users
WHERE profile_data->'social' ? 'twitter';

-- Using ?| for any key existence
SELECT username
FROM users
WHERE profile_data->'social' ?| ARRAY['twitter', 'instagram'];

-- Using ?& for all keys existence
SELECT username
FROM users
WHERE profile_data->'social' ?& ARRAY['twitter', 'linkedin'];

📊 Part 3: Aggregating JSON Data

-- Aggregate interests into array
SELECT
    jsonb_agg(DISTINCT interest) AS all_interests
FROM users,
    jsonb_array_elements_text(profile_data->'interests') AS interest;

-- Count users by city
SELECT
    profile_data->'address'->>'city' AS city,
    COUNT(*) AS user_count
FROM users
GROUP BY profile_data->'address'->>'city'
ORDER BY user_count DESC;

-- Aggregate user data into JSON array
SELECT jsonb_agg(
    jsonb_build_object(
        'username', username,
        'name', profile_data->>'name',
        'city', profile_data->'address'->>'city'
    )
) AS users_json
FROM users;

✏️ Part 4: Updating JSON Data

-- Update a single field
UPDATE users
SET profile_data = jsonb_set(
    profile_data,
    '{age}',
    '29'::jsonb
)
WHERE username = 'alice';

-- Update nested field
UPDATE users
SET profile_data = jsonb_set(
    profile_data,
    '{address,city}',
    '"Boston"'::jsonb
)
WHERE username = 'alice';

-- Add new field
UPDATE users
SET profile_data = profile_data || '{"phone": "555-1234"}'::jsonb
WHERE username = 'alice';

-- Remove field
UPDATE users
SET profile_data = profile_data - 'phone'
WHERE username = 'alice';

-- Remove nested field
UPDATE users
SET profile_data = profile_data #- '{social,instagram}'
WHERE username = 'bob';

-- Update array element (replace first interest)
UPDATE users
SET profile_data = jsonb_set(
    profile_data,
    '{interests,0}',
    '"running"'::jsonb
)
WHERE username = 'alice';

🔄 Part 5: JSONB Aggregation Functions

-- jsonb_object_agg: Aggregate key-value pairs
SELECT
    jsonb_object_agg(
        username,
        profile_data->>'name'
    ) AS user_names
FROM users;

-- jsonb_build_object: Build JSON objects
SELECT
    username,
    jsonb_build_object(
        'displayName', profile_data->>'name',
        'location', profile_data->'address'->>'city',
        'memberSince', created_at
    ) AS user_profile
FROM users;

-- Flatten nested JSON
SELECT
    username,
    profile_data->>'name' AS name,
    profile_data->'address'->>'city' AS city,
    jsonb_array_elements_text(profile_data->'interests') AS interest
FROM users;

📝 Part 6: XML Operations

Creating XML Data

-- Create XML data
CREATE TABLE products_xml (
    product_id SERIAL PRIMARY KEY,
    product_data XML
);

INSERT INTO products_xml (product_data) VALUES
('
<product>
    <id>1001</id>
    <name>Laptop Pro</name>
    <category>Electronics</category>
    <price currency="USD">1299.99</price>
    <specifications>
        <processor>Intel i7</processor>
        <ram>16GB</ram>
        <storage>512GB SSD</storage>
    </specifications>
    <tags>
        <tag>laptop</tag>
        <tag>professional</tag>
        <tag>high-performance</tag>
    </tags>
</product>
'),
('
<product>
    <id>1002</id>
    <name>Wireless Mouse</name>
    <category>Accessories</category>
    <price currency="USD">49.99</price>
    <specifications>
        <connectivity>Bluetooth 5.0</connectivity>
        <battery>2 years</battery>
    </specifications>
    <tags>
        <tag>mouse</tag>
        <tag>wireless</tag>
    </tags>
</product>
');

Querying XML Data

-- Extract XML elements
SELECT
    (xpath('/product/id/text()', product_data))[1]::text::int AS product_id,
    (xpath('/product/name/text()', product_data))[1]::text AS product_name,
    (xpath('/product/price/text()', product_data))[1]::text::decimal AS price
FROM products_xml;

-- Extract attributes
SELECT
    (xpath('/product/name/text()', product_data))[1]::text AS name,
    (xpath('/product/price/@currency', product_data))[1]::text AS currency,
    (xpath('/product/price/text()', product_data))[1]::text::decimal AS price
FROM products_xml;

-- Extract nested elements
SELECT
    (xpath('/product/name/text()', product_data))[1]::text AS name,
    (xpath('/product/specifications/processor/text()', product_data))[1]::text AS processor,
    (xpath('/product/specifications/ram/text()', product_data))[1]::text AS ram
FROM products_xml;

-- Extract array elements
SELECT
    (xpath('/product/name/text()', product_data))[1]::text AS name,
    UNNEST(xpath('/product/tags/tag/text()', product_data))::text AS tag
FROM products_xml;

Updating XML Data

-- Update XML element
UPDATE products_xml
SET product_data = xmlreplace(
    product_data,
    xpath('/product/price/text()', product_data),
    xml '<price>1199.99</price>'
)
WHERE (xpath('/product/id/text()', product_data))[1]::text = '1001';

-- Add new element
UPDATE products_xml
SET product_data = xmlconcat(
    product_data,
    xml '<warranty>2 years</warranty>'
)
WHERE (xpath('/product/id/text()', product_data))[1]::text = '1001';

🎯 Quiz Section

🏆 Best Practices for Interviews

💡✅ JSON/JSONB Best Practices

1. Use JSONB for Querying:

-- JSONB supports indexing and containment operators
CREATE INDEX idx_users_profile ON users USING gin(profile_data);

-- JSON is for storage only, not efficient querying

2. Create Indexes for Common Queries:

-- GIN index for containment queries
CREATE INDEX idx_profile_gin ON users USING gin(profile_data);

-- B-tree index for specific fields
CREATE INDEX idx_profile_age ON users ((profile_data->>'age')::int);

3. Validate JSON Structure:

-- Add CHECK constraint
ALTER TABLE users
ADD CONSTRAINT valid_profile
CHECK (profile_data IS jsonb);

4. Use Path Operators Carefully:

-- -> for single level
profile_data->'name'

-- ->> for text extraction
profile_data->>'name'

-- #>> for nested paths
profile_data#>>'{address,city}'

5. Consider Shallow vs Deep Structure:

-- Shallow: Easier to query, more duplication
{"city": "NY", "state": "NY"}

-- Deep: Less duplication, more complex queries
{"address": {"city": "NY", "state": "NY"}}

⚠️⚠️ Common Pitfalls

Using JSON instead of JSONB: No indexing support
Missing GIN index: Slow containment queries
Type casting errors: Use ->> for text, cast explicitly
Null handling: JSON null vs SQL NULL
Path typos: Wrong paths return NULL silently

Topic: JSON and XML Operations in SQL for FAANG Interviews