How to map a column to JSONB instead of JSON, with Doctrine and Postgresql?

Question

In my project I'm using Doctrine doctrine 2.5.4/postgres 9.5. I'm trying to add a jsonb field using YAML:

fields:
  obj: json_array

This gets interpreted as a json column type (and not jsonb). The specification notes about picking up json or jsonb:

Chosen if the column definition contains the jsonb option inside the platformOptions attribute array and is set to true.

But platformOptions doesn't seem to work (tried to add it below obj, at the top... with no success). How can I add a jsonb field?

Seems like there is no way to do that (just checked in the source for the same doctrine/dbal version) — zerkms
– zerkms, Commented May 30, 2016 at 3:31

Trece · Accepted Answer · 2022-08-26 09:02:31Z

19

This is supported by doctrine/dbal v2.6+ (it requires PHP 7.1). All you need to do is use json_array and set options={"jsonb"=true} I tested this on doctrine/dbal v2.6.3

This is what it looks like in PHP format:

/**
 * @ORM\Column(type="json_array",nullable=true,options={"jsonb"=true})
 */
  private $details;

and it creates a query such as (for an existing table):

ALTER TABLE mytable ADD details JSONB NOT NULL;

More details about type mapping can be found at Doctrine mapping matrix.

edited Aug 26, 2022 at 9:02

Trece

356 bronze badges

answered Dec 4, 2017 at 13:27

Evren Yurtesen

2,3691 gold badge24 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kevin Over a year ago

It worked! Can you give me more document about options values? I can't not find anything about "jsonb"=true in official document.

Evren Yurtesen Over a year ago

@Geany I have added the link to mapping matrix to the answer. But json_array is deprecated... :)

Maxim Mandrik Over a year ago

@EvrenYurtesen use type="json", options={"jsonb": true}

Maxim Mandrik Over a year ago

@Geany Only for PostgreSQL. In source code github.com/doctrine/dbal/blob/3.1.x/src/Platforms/… PostgreSQL100Platform extends PostgreSQL94Platform if (!empty($column['jsonb'])) { return 'JSONB';} return 'JSON';

o4ned Over a year ago

even cleaner approach with PHP >= 8.0 and attributes would be #[ORM\Column(type: Types::JSON, nullable: true, options: ['jsonb' => true])]

aferber · Accepted Answer · 2017-01-22 10:31:21Z

1

Use boldtrn/jsonb-bundle, it provides a jsonb doctrine type as well as custom functions to access the special operators provided by the PostgreSQL jsonb data type.

answered Jan 22, 2017 at 10:31

aferber

1,16110 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to map a column to JSONB instead of JSON, with Doctrine and Postgresql?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related