PostgreSQL: Can you use JSON_GET on JSONB columns deeper than one layer?

[dzuban]

Like the title says, can you use JSON_GET on JSONB columns deeper than one layer for PostgreSQL databases?

I have a table called sales_order that contains a JSONB column called sales_order_data. This column contains json roughly in the shape of:

{
  "entity": {
    "billing_address": {
      "last_name": "foobar"
    }
  },
  "identifier": "1234"
}

Via regular queries directly in my PostgreSQL shell like
SELECT * FROM sales_order WHERE sales_order_data->'entity'->'billing_address'->'last_name' ? 'foobar';
I am able to retrive data (I know that this would be closer to JSONB_EXISTS).

But I seem to be unable to replicate the same behavior via Doctrine QueryBuilder and this extension.
Funnily enough I am able to access the "identifier" field with

$q = $this->createQueryBuilder('so')
            ->select('so')
            ->where("JSON_GET_TEXT(so.salesOrderData, 'identifier') = :identifier")
            ->setParameter('identifier', '1234')
            ->getQuery()->getArrayResult();

but I couldn't for example get billing_address and last_name below it which is why I have the feeling, that this library can only go down one layer into JSON data.

Is that the case?

[Hikariii]

This library just allows you to use the SQL functions. It only unlocks the json functions and allows you to use them within Doctrine (with Doctrine Query Language).

I have no experience with postgres, but i would suggest you check the SQL output of your DQL query.
For some extra help, read: https://www.postgresql.org/docs/9.5/functions-json.html

From these examples it looks like this maybe makes more sense:

@> | jsonb | Does the left JSON value contain within it the right value? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

In your case:

WHERE sales_order_data @> '{"last_name":"foobar"}' = true

That would translate to something like:

->where("JSONB_CONTAINS(so.sales_order_data, :lastName) = true")
->setParameter('lastName', 'foobar');

But for the specifics I would try a unittest here and check the SQL output or log the doctrine query, sent to postgres