After learning the essentials of **JSON functions** in SQL, you are ready to tackle a practical challenge that involves working with structured data stored in a `JSON` column. The `user_profiles` table contains user information, where the `profile_data` column holds `JSON` objects. Each object includes user attributes such as `age`, `address`, and most importantly for this task, an `interests` array. Your goal is to extract this `interests` array for each user using SQL's JSON functions.

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_all_user_ids_returned(self):
        """Check that all user IDs are present in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        user_ids = set(row[0] for row in rows)
        self.cursor.execute("SELECT user_id FROM user_profiles;")
        expected_ids = set(row[0] for row in self.cursor.fetchall())
        _dynamic_test(
            self,
            user_ids == expected_ids,
            "All user IDs are returned in the result.",
            f"Missing or extra user IDs in result. Expected: {expected_ids}, Got: {user_ids}"
        )

    def test_interests_array_extracted(self):
        """Check that the interests array is correctly extracted for each user."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        result_rows = self.cursor.fetchall()
        self.cursor.execute("SELECT user_id, profile_data->'interests' FROM user_profiles;")
        expected_rows = self.cursor.fetchall()
        result_dict = {row[0]: row[1] for row in result_rows}
        expected_dict = {row[0]: row[1] for row in expected_rows}
        _dynamic_test(
            self,
            result_dict == expected_dict,
            "Interests array is correctly extracted for every user.",
            "Interests array is not correctly extracted for every user."
        )

    def test_result_columns(self):
        """Check that the result includes both user_id and interests columns."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        _dynamic_test(
            self,
            ('user_id' in columns and 'interests' in columns),
            "Result includes both user_id and interests columns.",
            f"Result columns are: {columns}, expected: user_id and interests."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Extract User Interests

Solution

Challenge: Extract User Interests

Solution