PostgreSQL - NoSQL query ideas to select one node of JSON

I have this JSON on my database:

  {
      "Id": 1,
      "Questions": [
        {
          "QuestionId": 6,
          "Description": "Question 1",
          "Alternatives": [
            {
              "Index": 1,
              "CorrectAnswer": false,
              "AlternativeId": 26,
              "QuestionId": 6,
              "Description": "Alternative one",
              "Selected": false
            },
            {
              "Index": 2,
              "CorrectAnswer": true,
              "AlternativeId": 27,
              "QuestionId": 6,
              "Description": "Alternative two",
              "Selected": false
            }
          ]
        },
        {
          "QuestionId": 7,
          "Description": "Question 2",
          "Alternatives": [
            {
              "Index": 1,
              "CorrectAnswer": false,
              "AlternativeId": 26,
              "QuestionId": 6,
              "Description": "Alternative one",
              "Selected": false
            },
            {
              "Index": 2,
              "CorrectAnswer": true,
              "AlternativeId": 27,
              "QuestionId": 6,
              "Description": "Alternative two",
              "Selected": false
            }
          ]
        }
      ]
    }

I can not get just ONE question in this document. I tried the following queries:

select data#>'{Questions}' from db.my_table
where (data #> '{Questions,0,QuestionId}')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE (data -> 'Questions' ->> 'QuestionId')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE data  -> 'Questions' ->> 'QuestionId' = '6'

What can I be doing wrong? I always get the return of 0 affected rows.

Documentation:

https://www.postgresql.org/docs/current/static/datatype-json.html https://www.postgresql.org/docs/current/static/functions-json.html

Every help is welcome!

Solution

Let's talk about each of your queries first:

select data#>'{Questions}' from db.my_table
where (data #> '{Questions,0,QuestionId}')::numeric = 6;

This actually fails (ERROR: cannot cast type json to numeric). You can change the operator in the where clause to #>>, and it will no longer error -- #>> returns text, which can be cast to numeric. At that point the query will return your entire Questions array, however, rather than the specific question you want.

Why? Because all the questions are part of the same row.

SELECT returns rows, and its WHERE clause operates on rows. Since you have many values in a single row, you're going to have trouble conditionally extracting a json subobject with this approach.

The other two queries run successfully but produce no results:

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE (data -> 'Questions' ->> 'QuestionId')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE data  -> 'Questions' ->> 'QuestionId' = '6'

In both cases, you're indexing a nonexistent field in your json blob. Questions is an array, so indexing it with a string doesn't work, but json is loose enough that it lets you try. In other words, the clause WHERE data -> 'Questions' ->> 'QuestionId' = '6' simply doesn't match any rows.

The trick is to make each question into a row. That's a bit complicated, but not too hard to do.

First, look at the function json_array_elements, which seems like it should work -- it takes a json array and returns each element as a row with a single column (named "value"). At first glance, it seems like you should be able to do something of the form:

SELECT value FROM json_array_elements(...)
WHERE value ->> 'QuestionId'::numeric = 6;

Unfortunately, it's not quite so simple as that. Here's one query that works:

SELECT datatable.question
  FROM my_table,
    json_array_elements(my_table.data -> 'Questions') AS datatable(question)
 WHERE (question->>'QuestionId')::numeric = 6;

Okay, let's break that down a bit. Ultimately, it's returning the question field of the datatable table that we create using the json_array_elements call. question is a json object, and we filter to only return the objects where QuestionID == 6.

Note, though, that we're also selecting from my_table, because ultimately that's where the data comes from. In this case, we're making an unconstrained cartesian join between my_table and datatable. That's not good, and probably won't scale well. But it works for our current situation of only a few rows.

Hope that gives you a place to start.