Get a value in JSON

[QuentinCurtet]

Hi,

I wanted to know how i can get a value in JSON like : SELECT * FROM products WHERE (data->>'in_stock')::integer > 0;

For the moment i have this, but the result of query is empty :
$rsm = new ResultSetMapping();
$query = $this->_em->createNativeQuery("SELECT p.attributes FROM Product p WHERE (p.attributes->>'price')::float > ?;", $rsm);
$query->setParameter(1, 9);
$result = $query->getResult();

Thanks.

[QuentinCurtet]

And the stored data is a JSON string, not an array, so i can't search on it.
Some help please ?

[boldtrn]

@QuentinCurtet Have you set up the Bundle as described in the documentation and defined it as jsonb. So this should make the column JSONB. Have a look in the tests for a sample setup. Debug the query from Doctrine to see the raw sql and check if that is correct or not. Basically everything should work as described in the docs.

[QuentinCurtet]

Hello,

Thanks for your answer.
I set up the bundle as described in the documentation, and in fact it is defined as jsonb in my Postgre DB.
The problem is that the JSON is not stored as an array but as a string so i can't use the Postgre functions...

I'll look in the tests the setup, and if i don't find anything, i'll be back ^^

[QuentinCurtet]

My code seems OK :
`class ProductController extends Controller
{
/**
* @return Response
*/
public function insertAction()
{
$em = $this->getDoctrine()->getManager();
$pen = new Product();
$pen->setAttributes('{"id":"1","name":"Stylo","price":"9.99","color":"red"}');

    $bag = new Product();
    $bag->setAttributes('{"id":"2","name":"Sac","price":"29.99"}');

    $em->persist($pen);
    $em->persist($bag);

    $em->flush();

    return new Response($pen->getAttributes().' and '.$bag->getAttributes());
}

/**
 * @return Response
 */
public function selectAction()
{
    $em = $this->getDoctrine()->getManager();
    $repository = $em->getRepository('AppBundle:Product');
    $results = $repository->selectDB();

    return $this->render('AppBundle:Product:index.html.twig', ['results' => $results]);
}

}`

And in my repository :
`class ProductRepository extends \Doctrine\ORM\EntityRepository
{
/**
* @return array
*/
public function selectDB()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle\Entity\Product', 'p');
$rsm->addFieldResult('p', 'id', 'id');
$rsm->addFieldResult('p', 'attributes', 'attributes');
$query = $this->_em->createNativeQuery("SELECT p.id, p.attributes FROM Product p WHERE (p.attributes->>'price')::float > 9", $rsm);

    return $query->getResult();
}

}`

But the result is empty.

Some help ?

[QuentinCurtet]

The problem is caused by the json_encode of the json_array type that add some double quotes and backslashes and when the json is decoded, it's not an array...

[QuentinCurtet]

I fix the problem by creating my own type by creating JsonB type which extends type and doesn't use json_encode when inserting data.

[boldtrn]

Perfect, not sure why you were the first to report this problem, it seems it works for most people :/. Anyway, nice fix.

[QuentinCurtet]

In fact, i think this was the JSON i tried to insert in my DB.
In the Postgre request, i tried to insert a JSON and not an object to encode in JSON so it was encoded twice and decoded once...
I have overloaded the JsonArrayType to handle all the case :

• Insert a JSON directly
• Insert an object

Thanks for your help it helped me :)