CODEWITHSUNDEEP
sqlpostgresqljsonbjsonpathset-returning-functions
Isaac N
Isaac N

Reputation: 159

PostgreSQL jsonb_path_query removes result instead of returning null value

In an example table:

CREATE TABLE example (
    id   SERIAL PRIMARY KEY,
    data JSON NOT NULL );
INSERT INTO example (id, data) VALUES
  (1, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]')
, (2, '[{"key": "1", "value": "val1"}]')
, (3, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]');

I want to query the value field in the data column where key = 2. The query I'm currently using is this:

SELECT id,
       jsonb_path_query(
               TO_JSONB(data),
               '$[*] ? (@.key == "2").value'::JSONPATH
           )::VARCHAR AS values
FROM example

I would expect the results to be:

id values
1 "val2"
2 null
3 "val2"

But the actual result is:

id values
1 "val2"
3 "val2"

Is there a reason why the null output of jsonb_path_query() is omitted?
How do I get it to behave the way I'm expecting?

Upvotes: 5

Views: 2001

Answers (3)

Zegarek
Zegarek

Reputation: 26322

Scalar function processes a value and it can nullify it, but a set-returning function generates values, so it can end up not generating one at all. As already suggested, you can use the scalar function

SELECT id, jsonb_path_query_first(data::jsonb, '$[*]?(@.key=="2").value')
FROM example;

Alternatively, you can keep the SRF, Set-Returning Function jsonb_path_query()→ setof jsonb by wrapping it in a scalar subquery or replacing its implicit inner join. Both will indicate lack of generated values with a null, but the latter can also still spawn and return multiple: demo

SELECT id,(SELECT jsonb_path_query(data::jsonb,'$[*]?(@.key=="2").value')LIMIT 1)
FROM example;

SELECT id, values
FROM example LEFT JOIN jsonb_path_query(data::jsonb, '$[*]?(@.key=="2").value') 
                       AS srf(values) 
             ON true;

Row 2 wasn't generating any rows in that function, which is why you didn't see it - there was no row to have a null in. Select section is not a normal place for an SRF - when applied there, it ends up pushed down to from section to be inner joined with. And inner join with an empty set, is an empty set. That, combined with implicit join behaviour, is why you can read:

  • SELECT srf(); as an actual SELECT b.x FROM srf() AS b(x);

  • SELECT a.id, srf() FROM a; meaning SELECT a.id, b.x FROM a, srf() AS b(x);

  • SELECT a.id, b.x FROM a, srf(a.v) AS b(x); and SELECT a.id, srf(a.v) FROM a; actually meaning:

    SELECT a.id, b.x FROM a INNER JOIN srf(a.v) AS b(x) ON true;

This INNER JOIN threw out your ids that didn't have result-generating data. LEFT JOIN won't.

Another illustration with a more obvious generate_series() "give me this many rows" SRF: demo

create table table1(id smallserial, how_many_to_generate int);
insert into table1(how_many_to_generate) values (1),(0),(2),(0),(1);

--no rows for the 2nd and 4th entry where `how_many_to_generate` is 0
--on the other hand, the 3rd entry requested and received two rows
select id, how_many_to_generate, generate_series(1, how_many_to_generate)
from table1;
-- id | how_many_to_generate | generate_series
------+----------------------+-----------------
--  1 |                    1 |               1
--  3 |                    2 |               1
--  3 |                    2 |               2
--  5 |                    1 |               1

Upvotes: 9

user330315
user330315

Reputation:

You want jsonb_path_query_first() if you want the result of the path expression:

SELECT id,
       jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') AS values
FROM example

Note that this returns a jsonb value. If you want a text value, use:

jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}

Upvotes: 5

Ftisiot
Ftisiot

Reputation: 1868

As per PostgreSQL documentation the filter acts as WHERE condition

When defining the path, you can also use one or more filter expressions that work similar to the WHERE clause in SQL. A filter expression begins with a question mark and provides a condition in parentheses:

I managed to achieve what you're looking for using the LATERAL and a LEFT JOIN

SELECT id,
         *
FROM example left join 
LATERAL jsonb_path_query(
        TO_JSONB(data),
        '$[*] ? (@.key == "2").value'::JSONPATH) 
    on true;

Result

 id | id |                              data                              | jsonb_path_query 
----+----+----------------------------------------------------------------+------------------
  1 |  1 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
  2 |  2 | [{"key": "1", "value": "val1"}]                                | 
  3 |  3 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
(3 rows)

Upvotes: 0

Related Questions