PostgreSQL json fields, how to use them? #252

Activity

1. dg commented on Feb 13, 2020

   

   dg

   on Feb 13, 2020

   Member

   More actions

   Because keys are always quoted, I think the best way is to use Nette\Database\SqlLiteral

   $db->query('SELECT * FROM log WHERE', [
   	new SqlLiteral("data->>'code' = ?", [2]),
   ]);
2. badmansan commented on Feb 13, 2020

   

   badmansan

   on Feb 13, 2020

   Author

   More actions

   Maybe you're right. I just don't like how the resulting code looks in case of complex queries.

   $i = 0;
   
   foreach ($keyIds as $key) {
   	$literal = [
   		$this->db::literal("data->>'code' = ?", $event),
   		$this->db::literal("data->'data'->>'key' = ?", $key),
   	];
   
   	if ($result) {
   		$literal[] = $this->db::literal("data->'data'->>'result'", $result);
   	}
   
   	$params[$i++] = $this->db::literal('?and', $literal);
   }
   
   $this->query($sql, $this->db::literal('?and', $params));

   instead of

   foreach ($keyIds as $key) {
   	$params = [
   		"data->>'ev_code'" => $event,
   		"data->'data'->>'key'" => $key,
   	];
   
   	if ($result) {
   		$params[] = ["data->data'->>'result'" => $result];
   	}
   }
   
   $this->query($sql, ...$params);
3. dg commented on Feb 14, 2020

   

   dg

   on Feb 14, 2020

   Last edited by dg

   Member

   More actions

   Yes, problem is that data->>'code' is not column, it is column data and JSON operator ->> and string 'code'. And NDB is not ready for it. Support for JSON could certainly be added…
4. otuhacek commented on Mar 16, 2021

   

   otuhacek

   on Mar 16, 2021

   Contributor

   More actions

   I have similar problem. When filtering with fields->>'myNote' LIKE (?) it wraps myNote with quotes "fields"->>'"myNote"' LIKE (?). So I need to go with SqlLiteral for now. Support for these specialities would be nice.