PostgreSQL json fields, how to use them?

[badmansan]

Version: 3.0.5

Bug Description

I want to use json field in WHERE

Steps To Reproduce

Create simple table:

CREATE TABLE "log" (
    "data" json NOT NULL
);

INSERT INTO "log" ("data") VALUES
('{"code":2}');

Try to run query:

$db->query('SELECT * FROM log WHERE', [
	"data->>'code'" => 2,
]);

Expected Behavior

Result SQL must be (query in postgres_query.log)

SELECT * FROM log WHERE (data->>'code' = $1)

but I got

ERROR:  column "data->>'code'" does not exist at character 26
STATEMENT:  SELECT * FROM log WHERE ("data->>'code'" = $1)

Possible Solution

Nette need to somehow avoid quotes in the name of such fields

[dg]

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]),
]);

[badmansan]

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);

[dg]

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…

[otuhacek]

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.