Search code examples
sqlpostgresqljsonb

Trouble selecting text or numbers from a JSON stored in a postgresql DB

This is a followup from this question I asked earlier: Querying an array of objects in JSONB

I have a table with a column of the data type JSONB. Each row in the column has a JSON that looks something like this:

[
   {"A": {"AA": "something", "AB": false}},
   {"B": {"BA":[{"BAAA": [1,2,3,4]},{"BABA": {"one": 42}}]}}
]

Note: the JSON is a complete mess of lists and objects, and it has a total of 300 lines. Not my data but I am stuck with it. :(

I am using postgresql version 12

I am trying to write a query to return the value of AA (A->AA). This is the query I tried:

select data @@ '$[*].A.AA' from test

That just returns null.

But when I run this query:

select data @@ '$[*].A.AB' from test

It correctly returns false.

Question:

  • Why does the query return a value when the return type is a boolean and return null when the return type is a text?
  • How do I write a query to return text?

Some sample data to play with: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=fec0ba6e9bd6dffb9874e20ef7abab1d

Solution

  • You are misunderstanding what the @@ does. It checks if the JSONPath expression is true. It does not return the value at that path.

    A path expression like '$[*].A.AB' does not really make sense for the @@ operator as it does not contain any conditional operators like == or < or > that could be evaluated.

    '$[*].A.AB' just happens to work because the value at that path is a boolean. If that was e.g. an integer, it would also return null

    If you want to pick the value through a JSON path function, you need to use jsonb_path_query_first()

    select jsonb_path_query_first(data, '$[*].A.AB') 
from test

    Note that the result is again a JSONB value, not a text value. If you want it as text use jsonb_path_query_first(data, '$[*].A.AB') #>> '{}'

    dbFiddle

    It would indeed be nice if there was an operator like #> that accepts a JSONPath to extract the value at that path, but currently we have to live with the clumsy jsonb_path_query_first()