PostgreSQL: Support JSON data type

Tagging...

The only problem I see here is that we are currently going to bump PG min version to 9.1.2 but JSON support starts with 9.2 ;-) Please see #2477413: Increase minimum version requirement for Postgres to 9.1.2.

Suppose this no more issue, we just need to define "pgsql_type" for field

Fix for contrib module #2480201: Clean schema implementation for field schema

9.1 no more supported https://www.postgresql.org/support/versioning/ so looks it's time to up suggested version to 9.2
This needs check across main distrosbut anyway this issue can have BC implementation for this types

Sort of that may work

It might be good to add a method/property to the abstract Connection class for detecting that capability with a default of FALSE. Then it could be added to PostgreSQL and MySQL drivers. Since that class is abstract we should be able to add that to the API, right?

@mradcliffe Good idea, getCapabilities() makes sense! But then we need some flags to return.
For example this may be helpful for #2839683: Add support distinct on construction

Created #2846994: Increase minimum version requirement for Postgres to 10 and require the pg_trgm extension. In PostgreSQL 9.2 are JSON and JSONB data types supported.

Jotting down some notes from a sandbox project of mine.

We can check JSON capabilities with something like the following. I wrote this for Drupal 7. :-)

• mysql: 'SELECT JSON_VALID(:value)', [':value' => 'null']
• pgsql: 'SELECT pg_typeof(:value::jsonb)', [':value' => '{}']

And if an exception is thrown, then we can set/cache false or something.

Implementing this should be unblocked now, but might be duplicated by work done in #3046696: Move from serialized columns to JSON encoded data wherever possible, or use allowed_classes.

Since minimum version is still 9.1, some sort of capabilities detection needs to be added to DBTNG.

Re-rolled for 9.2.x

It just needs tests which could be extended for mysql (sqlite not clear)

+++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
@@ -455,7 +455,12 @@ public function getFieldTypeMap() {
+    if (version_compare($this->connection->version(), '9.2', '>=')) {
+      /**  */

This condition no longer needed for 9.0+
https://www.drupal.org/node/2847322

I'm working on adding some tests for this today.

I uploaded a test-only patch, and then a patch that makes the suggested changes from @andypost in #32.

I removed the Needs tests tag.

I updated the issue summary with the template so the current proposed resolution is clear.

+++ b/core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php
@@ -275,6 +275,80 @@ public function testSchema() {
+         ->values(['test_field' => $insertData])
+         ->execute();

Resolved coding standard issue in this patch. No major changes.

Thank you, interdiff. I need to look at it before uploading. :(

I created the patch from the test-only codebase. I re-applied the patch and fixed the code standard issue.

Looks ready for commiter

Re-running tests. I think the patch is failing because json and jsonb fail spell check and need exceptions.

It is the word jsonb. See: https://www.drupal.org/pift-ci-job/1923087

Adds jsonb to dictionary. Stays at RTBC if it passes.

fwiw https://www.drupal.org/project/json_field exists and implements a field type for Postgres's jsonb format as well as the json format.

I think for something to get core inclusion we need to implement it for all core drivers - ie. #3109340: [policy] Decide whether to require json support for all database drivers for Drupal 10 needs to get agreed first and then we need to decide how this is implemented. Going to discuss with other framework managers.

I think we should at least postpone this on #3109340: [policy] Decide whether to require json support for all database drivers for Drupal 10. I don't think we have to implement support for each database driver simultaneously in the same issue, but we should have viable patches for each so that we can see what it looks like - i.e. whether we need an abstraction layer or not to enable modules to use json/jsonb regardless of driver.

Policy landed

I commented in the MySQL issue, 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 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 #3373370: Support JSON fields in Sqlite schema

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: #3343634: Add "json" as core data type to Schema and Database API

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). So, anything currently using json_encode/json_decode and a text field is already not able to have duplicate object keys.
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. The options to use a text field and rely on PHP's encode/decode behaviour, or set pgsql_type to json are available if key order is an issue for a particular case.

Per #3343634-14: Add "json" as core data type to Schema and Database 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.