B
B
BonBon Slick2019-01-30 13:48:06
PostgreSQL
BonBon Slick, 2019-01-30 13:48:06

How to choose where json matters?

Postgresl, json column type, data format "[]"or "[1,2,3]"
You need to choose where it is empty, or there is a value, say 1 , in the json array of the column.

$results = $this->createQueryBuilder('notificationTableAlias')
            // ->select('notificationTableAlias.statuses::jsonb AS statuses')
            // ->andWhere('statuses::json = "[]"::json')
            ->andWhere('notificationTableAlias->"statuses"->>"values"::jsonb = :statuses::jsonb')
            // ->andWhere('notificationTableAlias->"statuses"->>"values"::jsonb = :statuses::jsonb')
            // ->andWhere('notificationTableAlias->statuses->values::jsonb = :statuses::jsonb')
            // ->andWhere('notificationTableAlias.statuses.values::text = :statuses::text')
            // ->andWhere('notificationTableAlias.statuses.values::jsonb = :statuses::jsonb')
            // ->andWhere('notificationTableAlias.statuses.values::jsonb = "[]"::jsonb')
            // ->andWhere('notificationTableAlias.statuses.values::text = "[]"::text')
            // ->andWhere('notificationTableAlias.statuses.values::text = "[]"')
            // ->andWhere('notificationTableAlias->"statuses"->"values" = "[]"')
            // ->andWhere('CAST(notificationTableAlias -> statuses ->> values AS TEXT) = "[]"')
            // ->andWhere('notificationTableAlias.statuses.values::text::jsonb = :status::text::jsonb')
            // ->setParameter('statuses', \json_encode([]))
            ->setParameter('statuses', '[]')
            ->getQuery()
            ->getScalarResult();
 dump($results);
 die;

None of the above works. Mistakes everywhere, for example
An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 124: Error: Expected Literal, 
got '>'").

or
An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 139: 
Error: Expected =, <, <=, <>, >, >=, !=, got ':'").

$results = $this->createQueryBuilder('notificationTableAlias')
            ->getQuery()
            ->getSQL()

result
SELECT n0_.id AS id_0, n0_.action AS action_1,
 n0_.created_at AS created_at_2, n0_.updated_at AS updated_at_3, 
n0_.statuses_values ​​AS statuses_values_4, n0_.use

Tried from here
https://stackoverflow.com/questions/32843213/opera...
https://hackernoon.com/how-to-query-jsonb-beginner...
https://stackoverflow.com/questions/10560394/ how-d...
https://stackoverflow.com/questions/32843213/opera...
I don't understand why it gives an error when it works for others, judging by the answers to the stack or the docs

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question