doctrine JSON column in postgresql is storing objects or arrays of the same data [duplicate]

Question

I have a Symfony application, the User [doctrine] entity has the property roles, it should be an array or strings.

    /**
     * @ORM\Column(type="json")
     */
    private $roles = [];

    public function getRoles(): array
    {
        $roles = $this->roles;
        // guarantee every user at least has ROLE_USER
        $roles[] = 'ROLE_USER';

        return array_unique($roles);
    }

    public function setRoles(array $roles): self
    {
        $this->roles = $roles;

        return $this;
    }

It was a long time ago, but I am pretty sure this part (code above) is what I got with php bin/console make:user as per the documentation. As you can see, it always is an array.

I have reached a point in my app where I need to select Users based on a role, but the problem I am faicing is that the db data is Inconsistent.

["ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]
["ROLE_TRANSLATOR","ROLE_DATA_ENTRY","ROLE_SITE_DIRECTOR"]
["ROLE_DATA_ENTRY"]
[]
{"0":"ROLE_SITE_DIRECTOR","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY"}
["ROLE_SPONSOR_REP"]
["ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]
{"0":"ROLE_SPONSOR_REP","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY","3":"ROLE_SITE_DIRECTOR"}

As a result, if I am looking for ROLE_TRANSLATOR I have to check in an array, or every object key

WHERE u.roles::jsonb ? 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '0' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '1' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '2' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '3' = 'ROLE_TRANSLATOR'

This will only get worse as I add more roles. Currently there are only 4.

My Questions;
Why is doctrine being Inconsistent? And, can I fix it? OR
How can I make a simpler WHERE clause?

as far as the rest of the aplication goes, roles and security works as expected.

Chad · Accepted Answer · 2021-03-15 19:13:12Z

2

Mad props to @Dave Redfern on Slack who pointed out my problem. When passing a non-zero indexed array, it is interperted as an object.

dump(json_encode([
    0 => "ROLE_SITE_DIRECTOR", 2 => "ROLE_TRANSLATOR", 1 => "ROLE_DATA_ENTRY",
]));
dump(json_encode(array_values([
    0 => "ROLE_SITE_DIRECTOR", 2 => "ROLE_TRANSLATOR", 1 => "ROLE_DATA_ENTRY",
])));

output will be:

"{"0":"ROLE_SITE_DIRECTOR","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY"}"
"["ROLE_SITE_DIRECTOR","ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]"

So the fix is simple, im my setter:

$this->roles = array_values($roles);

Moving forward, it is also a better practice to use database relationships to the roles. Searching by JSON data is not fun, but in a join is standard practice.

answered Mar 15, 2021 at 19:13

Chad

1,1991 gold badge17 silver badges42 bronze badges

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

1 Comment

rkeet Over a year ago

Thanks for documenting this :) Had me stumped for a bit, was wondering what I'd done wrong with the Doctrine or SF Serializer config.

Collectives™ on Stack Overflow

doctrine JSON column in postgresql is storing objects or arrays of the same data [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related