MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

SQL (PostgreSQL, MySQL, SQLite) Module 15

JSON in SQL interview questions

Interview questions on JSON in SQL: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

16 questions: 4 warm-ups and 12 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher to mid

    What is the difference between json and jsonb in PostgreSQL?

    Answered in the lesson Storing JSON: json, jsonb and JSON columns

  2. Fresher to mid

    What is the difference between -> and ->> when you read a value from a JSON column?

    Answered in the lesson Querying JSON values and paths

  3. Fresher to mid

    What do json_each() in SQLite and JSON_TABLE in PostgreSQL and MySQL do, and how do they differ?

    Answered in the lesson Turning JSON into rows and rows into JSON

  4. Fresher to mid

    What is the difference between json_set, json_insert and json_replace?

    Answered in the lesson Updating and indexing JSON

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Mid to senior

    A mobile app keeps payment events in SQLite on the phone and sends the same JSON text to a server that stores it in a PostgreSQL jsonb column. For a few payments the phone shows 500 paise and the server 30,800 for the same event. What is going on, and how do you fix it for good?

    What it tests
    Repeated keys in JSON, how SQLite and PostgreSQL resolve them, finding the affected rows, and fixing the producer rather than one reader.
    First step
    Compare the stored text of one affected event on both sides. The phone keeps the document as written, so look for amount_paise appearing twice. SQLite 3.49.1 reads the first copy, while jsonb kept only the last one when the text was converted, so the two databases read different pairs of the same document.
    Follow-ups

    From the lesson Storing JSON: json, jsonb and JSON columns

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  2. Mid to senior

    A marketplace sells phones, sarees and books, and each category has its own attributes: RAM and storage, fabric and length, author and ISBN. A teammate proposes one jsonb column for everything except the product id. What do you keep as real columns, what goes into JSON, and how do you keep the JSON clean?

    What it tests
    Choosing between columns and JSON from the queries a system runs, constraints and validation in PostgreSQL, and a path for promoting a key into a column.
    First step
    List what the product pages, search filters and reports query. Fields that every product has, or that queries join, filter, sort or add up, such as category, seller, price and stock, stay real columns with types, foreign keys and CHECKs. Attributes that differ by category and are read mostly on the product page go into one jsonb column with a CHECK that it is an object.
    Follow-ups

    From the lesson Storing JSON: json, jsonb and JSON columns

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  3. Mid to senior

    A payment provider signs each webhook over the exact bytes of its body. You must keep every body for eight years, check its signature again during an audit, and let analysts query the bodies by event type. In PostgreSQL, do you store the body as json, jsonb, text or bytea, and why?

    What it tests
    What json and jsonb keep of a document, why a signature needs the exact bytes, bodies that are not valid JSON, and separating an audit copy from a copy for queries.
    First step
    A signature covers exact bytes, so the audit copy must give back the same bytes. jsonb drops spaces, reorders keys and rewrites numbers, so it can never be that copy. Keep the raw body as received, in bytea (or text when it is always valid UTF-8), and give analysts a jsonb copy or a few columns derived from it.
    Follow-ups

    From the lesson Storing JSON: json, jsonb and JSON columns

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Mid to senior

    A SQLite report counts UPI payments over 1,000 rupees with WHERE props ->> 'amount' > 1000. The day after a new app release, the count jumps: almost every payment from the new version is included, even 50-rupee ones. What happened, and how do you fix the query and the data?

    What it tests
    How SQLite compares values of different storage classes, what ->> returns for a JSON string, and fixing the query, the stored rows and the producer.
    First step
    Compare the stored type of the amount in an old and a new payment with json_type(props, '$.amount'). Old versions sent a JSON number, which ->> returns as an INTEGER; the new one sends a string such as "50", which ->> returns as TEXT, and SQLite ranks every TEXT value above every number, so each of those payments passes the filter.
    Follow-ups

    From the lesson Querying JSON values and paths

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Mid to senior

    A report counts orders without a coupon with doc ->> 'coupon' IS NULL. On PostgreSQL it says 1,840 orders; after the same data moves to MySQL 8.4, it says 1,215. No rows were lost. Explain the difference, and write a check that counts the same in both engines.

    What it tests
    What ->> returns for JSON null in each engine, telling a missing key from a key that holds null, and writing JSON checks that mean the same everywhere.
    First step
    Split the count into three states in both engines: the key is missing, the key holds JSON null, the key holds a code. Count each state separately, then compare what ->> returns for the second state in each engine. The difference between the two totals should be exactly one of those groups.
    Follow-ups

    From the lesson Querying JSON values and paths

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Senior to staff

    An events service moves from PostgreSQL 18 to MySQL 8.4. Its queries use props @> '{"platform": "android"}', props ? 'coupon', props -> 'tags' ? 'express' and (props ->> 'amount')::numeric > 1000. How do you rewrite each filter, and which results can change?

    What it tests
    Mapping jsonb operators to MySQL functions, the differences in null handling and type conversion between them, and testing a port before traffic moves.
    First step
    State what each filter means before translating it: containment of one key and value, a test that a key exists, a string inside an array, and a numeric comparison. Write the MySQL function for each meaning, then run both versions on the same sample, including JSON null, missing keys and amounts sent as text, and compare the rows.
    Follow-ups

    From the lesson Querying JSON values and paths

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  7. Mid to senior

    An endpoint builds each order's JSON with json_agg(json_build_object('sku', i.sku, 'qty', i.qty)) over orders LEFT JOIN order_items, grouped by order. Orders without items come out as "items": [{"sku": null, "qty": null}], and the app shows them with one blank item. Why, and how do you fix it?

    What it tests
    How a LEFT JOIN feeds an aggregate, FILTER and per-list subqueries, and what each engine's array aggregate returns over no rows.
    First step
    Run the join without the aggregate for one empty order. The LEFT JOIN returns one row for it, with every item column NULL, and json_agg turns that row into an object of nulls. Either the aggregate must not see that row, or the list must be built where no such row exists, in a subquery per order.
    Follow-ups

    From the lesson Turning JSON into rows and rows into JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Fresher to mid

    A daily report expands each order's tags JSON array and counts orders per tag. The finance team notices that the report covers fewer orders than the orders table holds, although many orders carry two tags. Which orders are missing, and how do you write the report so that every order is counted?

    What it tests
    What an inner join to an expanded array does to empty and missing arrays, LEFT JOIN ... ON true, and reconciling a report with its source table.
    First step
    Count the orders whose tags are an empty array, NULL or not an array at all: json_each() and jsonb_array_elements() return no rows for them, so the join removes them before GROUP BY. Then compare the number of distinct order ids in the expanded rows with count(*) of the orders table.
    Follow-ups

    From the lesson Turning JSON into rows and rows into JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  9. Senior to staff

    An API returns each order's items as a JSON array built with JSON_ARRAYAGG in MySQL 8.4. After an index on order_items (order_id, sku) is added, receipts list the items in a different order and a contract test fails. Nothing else changed. Explain what happened and make the order stable.

    What it tests
    Why an aggregate without ORDER BY follows the plan, how an index changes the plan, and stating an order in MySQL, PostgreSQL and SQLite.
    First step
    Compare EXPLAIN before and after the index. The GROUP BY now reads the new index, which delivers each order's rows sorted by SKU, and JSON_ARRAYAGG collects rows in the order they arrive. Its manual calls that order undefined, so the old order was never promised: the test relied on the old plan.
    Follow-ups

    From the lesson Turning JSON into rows and rows into JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  10. Senior to staff

    A PostgreSQL events table has 50 million rows and a new GIN index on props, created with jsonb_path_ops. Queries with WHERE props ->> 'platform' = 'android' and WHERE props ? 'coupon' still scan the whole table. Why, and what would you change?

    What it tests
    Which operators a GIN operator class supports, how an expression index is matched, and choosing indexes from the filters that actually run.
    First step
    Look at the operators. A GIN index on props serves operators on the jsonb value itself, and jsonb_path_ops serves only @>, @? and @@, with no entries for the key test ?. The filter ->> 'platform' = 'android' compares text extracted from the document, which no index on props can answer.
    Follow-ups

    From the lesson Updating and indexing JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  11. Mid to senior

    Two services update a customer's preferences, stored as one jsonb document: one sets the language, the other the SMS opt-out. Each reads the document, changes its key and writes the whole document back. Customers report that their language keeps switching back. What is happening, and how do you fix it?

    What it tests
    Lost updates from read-modify-write, changing one key inside the database, row locks and optimistic versions.
    First step
    Draw the timeline: both services read version 1, the language service writes version 2 with the new language, and the alerts service then writes its copy of version 1 with only the SMS change, so the language reverts. The last writer wins, and no error is raised anywhere.
    Follow-ups

    From the lesson Updating and indexing JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  12. Senior to staff

    Dashboards filter a 200-million-row PostgreSQL events table on props ->> 'platform', and each document is about 2 KB. When would you promote the key to a real column, and how do you do it without blocking writes?

    What it tests
    When a key deserves a column, generated columns and their locks in PostgreSQL 18, batched backfills and online index builds.
    First step
    Try the cheap fix first: an expression index on (props ->> 'platform'), built with CREATE INDEX CONCURRENTLY, may be enough. Then list what a real column would add, such as a type, a constraint, a foreign key or several indexes, and check what each way of adding the column locks and rewrites on a table this size.
    Follow-ups

    From the lesson Updating and indexing JSON

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track