Laravel 5.6. How to test JSON/JSONb columns

Question

$this->assertDatabaseHas() not working with JSON/JSONb columns.

So how can I tests these types of columns in Laravel?

Currently, I have a store action. How can I perform an assertion, that a specific column with pre-defined values was saved.

Something like

['options->language', 'en']

is NOT an option, cause I have an extensive JSON with meta stuff.

How can I check the JSON in DB at once?

Archeology

Answer 1

UPD

Now can be done like that.

---

I have solved it with this one-liner (adjust it to your models/fields)

$this->assertEquals($store->settings, Store::find($store->id)->settings);

Archeology

Answer 2

Laravel 7+

Not sure how far back this solution works.

I found out the solution. Ignore some of the data label, Everything is accessible, i was just play around with my tests to figure it out.

/**
 * @test
 */
public function canUpdate()
{
    $authUser = UserFactory::createDefault();
    $this->actingAs($authUser);

    $generator = GeneratorFactory::createDefault();

    $request = [
        'json_field_one' => [
            'array-data',
            ['more-data' => 'cool'],
            'data' => 'some-data',
            'collection' => [
                ['key' => 'value'],
                'data' => 'some-more-data'
            ],
        ],
        'json_field_two' => [],
    ];

    $response = $this->putJson("/api/generators/{$generator->id}", $request);
    $response->assertOk();

    $this->assertDatabaseHas('generators', [
        'id' => $generator->id,
        'generator_set_id' => $generator->generatorSet->id,

        // Testing for json requires arrows for accessing the data
        // For Collection data, you should use numbers to access the indexes
        // Note:  Mysql dose not guarantee array order if i recall. Dont quote me on that but i'm pretty sure i read that somewhere.  But for testing this works
        'json_field_one->0' => 'array-data',
        'json_field_one->1->more-data' => 'cool',

        // to access properties just arrow over to the property name
        'json_field_one->data' => 'some-data',
        'json_field_one->collection->data' => 'some-more-data',

        // Nested Collection
        'json_field_one->collection->0->key' => 'value',

        // Janky way to test for empty array
        // Not really testing for empty
        // only that the 0 index is not set
        'json_field_two->0' => null,
    ]);
}

Duckling

Hm...if this works, this is awesome! But, please, clean up your answer. It's really worth being posted, just...could you make it more convenient for other devs?)) – Archeology

Damn, I need this 'prompters->collectionA->0->not-working' => 'cant-access', to work. – Gibbsite

It works, ill update the words so its not confusing, that was me playing around and when it worked i just pasted the results. My bad. @ChristhoferNatalius – Duckling

@ShawnPivonka doesn't work for me, that's why I commented. idk why. I switched to assertEquals instead. – Gibbsite

@ChristhoferNatalius i think the issue is I'm using Laravel 7. Im guessing for 5.6 this might not work. What version of Laravel are you using? – Duckling

@ShawnPivonka I'm on Laravel 8. – Gibbsite

Answer 3

Note: The below solution is tested on Laravel Version: 9.x and Postgres version: 12.x and the solution might not work on lower version of laravel

There would be two condition to assert json column into database.

1. Object

Consider Object is in json column in database as shown below:

 "properties" => "{"attributes":{"id":1}}"

It can assert as

$this->assertDatabaseHas("table_name",[
    "properties->attributes->id"=>1
]);

2. Array

Consider array is in json column as shown below:

 "properties" => "[{"id":1},{"id":2}]"

It can assert as

$this->assertDatabaseHas("table_name",[
    "properties->0->id"=>1,
    "properties->1->id"=>2,
]);

Ausgleich

Just FYI, "properties->0->id"=>1, doesn't work in Laravel 9. Object — does. – Archeology

Interesting ! which database you are using? – Ausgleich

Right now it's MySQL 8. Checked and confirm, that nope. Still has to be old-fashioned. Object syntax works, soo...it's something 😅🤷🏻‍♂️ But funny, that the issue goes for 4 years. – Archeology

The above solution is tested on Postgres version: 12.x, as mentioned above, not sure about MySQL. – Ausgleich

🤔🤔🤔not too late to make a switch 😂😅 – Archeology

Answer 4

if you want to assert array in your databases, you can use something like this:

$this->assertDatabaseHas(ModelSettings::class, ['settings->array[0]' => 'value'])

Tympanist

Answer 5

Using json_encode on the value worked for me:

$this->assertDatabaseHas('users', [
    'name' => 'Gaurav',
    'attributes' => json_encode([
        'gender' => 'Male',
        'nationality' => 'Indian',
    ]),
]);

Paramorph