PostgreSQL: Support JSON data type

I commented in the MySQL issue ✨ Support JSON fields in MySQL schema Closed: duplicate , that MySQL has a single JSON type that is most similar to Postgres' jsonb, which could possibly cause some confusion or unexpected behaviour if Drupal's json:normal field type acts differently between database engines.
The META issue ✨ Add "json" as core data type Active might be the place to discuss the expected DB behaviour (raw vs. normalized), and whether Postgres' convention should be adopted by Drupal. (The Postgres documentation does recommend that jsonb be used as the default, unless legacy needs require the json data to not be normalized).

re-roll and changed json:binary for jsonb

Also it could use pgsql_type in spec to provide this variation to keep it inline with Mysql - Re #49

Patch also removes try/catch and ref to policy issue, added type-hints

I removed jsonb as it could be used providing pgsql_type and to keep the driver inline with others

Also copied the test to test every returned datatype from 📌 Support JSON fields in Sqlite schema Closed: duplicate

The difference is that bool returned as "true" when sqlite returns 1 (so pgsql results are typed)

fix CS

simplified test

fix cs

Thanks @andypost for pushing this forward an unifying on json:normal

The PostgreSQL documentation says about json vs. jsonb:

The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

(https://www.postgresql.org/docs/current/datatype-json.html)

I added that to the issue summary.

As the implemented data type here is now the same as for the other database engines (see meta issue), I'll set this RTBC.

But we should discuss, how to proceed with jsonb and potentially jsonpath (see https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSON...) for PostgreSQL.
Should that simply be provided by a contrib module? Or as a follow-up here?

Moving "Needs framework manager review" tag to the parent meta issue: ✨ Add "json" as core data type Active

1. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php
   @@ -466,6 +466,8 @@ public function getFieldTypeMap() {
   +      'json:normal' => 'json',
   

   Lets use the JSONB field instead of the older JSON field.

2. +++ b/core/modules/pgsql/tests/src/Kernel/pgsql/SchemaTest.php
   @@ -243,4 +243,57 @@ public function testPgsqlExtensionExists(): void {
   +    foreach ($test_data as $data) {
   +      $path = $data[0];
   +      $expected = $data[1];
   

   Can we rewrite this to foreach ($test_data as $path => $expected) {

3. +++ b/core/modules/pgsql/tests/src/Kernel/pgsql/SchemaTest.php
   @@ -243,4 +243,57 @@ public function testPgsqlExtensionExists(): void {
   +    $query->addExpression('test_field -> :list -> 1', NULL, [
   +      ':list' => 'list',
   +    ]);
   

   Can we change this to: $query->addExpression('test_field -> 'list' -> 1');

4. In the other 2 related issues the added testing has much overlap. Can we put that in a single test instead of duplecating the code.
5. In the other 2 related issues the value of $test_data is different. Can we make the testing values the same?

I don't think we can use jsonb out of box because it doing normalization and removing duplicate keys (even in arrays)

So if someone willing to use it they always can set it by pgsql_type

Still not clear if we can provide alternative to addExpression() method

+++ b/core/modules/pgsql/tests/src/Kernel/pgsql/SchemaTest.php
@@ -243,4 +243,57 @@ public function testPgsqlExtensionExists(): void {
+        $query->addExpression('[test_field] -> :path1 ->> :path2', NULL, [
...
+        $query->addExpression('[test_field] ->> :path', NULL, [
...
+    $query->addExpression('test_field -> :list -> 1', NULL, [

++ to move 2 arguments case out of loop to make it more clear

but the biggest question is the last case where 1 (array index) can't be passed as argument and that's why I did hardcoding it into query

I don't think we can use jsonb out of box because it doing normalization and removing duplicate keys (even in arrays)

I do not have a problem with removing duplicate keys. For me that is a plus. Doing normalization is also a plus for me. Far more important for me is the fact that you can do indexing on JSONB fields. Without an index we will be doing full table scans and that is a no-go for me.

I would agree that indexing is a huge consideration here and is a strong +1 for jsonb. It is also more read performant, per the docs, despite being slightly slower on initial input.

The normalization and de-duplicating array keys doesn't feel like a blocker; is there a practical example of where this would be an issue for current code?

The point about being able to opt to the alternative implementation (json vs. jsonb) is a good one but I think whatever we go with needs to be a sensible default for the 90+% usecase. It wouldn't hurt also to add in some assertions for input data that could be triggered at least in development, e.g. if you attempt to store data that will be truncated (e.g., duplicate keys) if the default implementation is jsonb.

+1 for jsonb

as I noted in #49:
- the postgres docs recommend jsonb
- mysql's json type doesn't preserve duplicate keys, does order object keys, and performs whitespace normalization

I don't think removing duplicate keys should be a concern - any value in a PHP data type won't have duplicate keys, and if an input to json_decode has a duplicate key only the last value will be preserved (same behaviour as postgres & mysql).
JSON objects are defined as being unordered, so any implementation using it shouldn't rely on order being preserved. It's probably only worth a documentation note that order preservation shouldn't be expected when storing and retrieving from json database fields.

Per #3343634-14: Add "json" as core data type for Schema API → this is being consolidated into the parent issue for a single change.

Also appears the jsonb change was made on the parent, resolving #63.