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?

D.R. · Accepted Answer · 2021-04-25T12:40:54.613

6

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);

edited Apr 25 '21 at 12:40

answered May 06 '18 at 18:55

D.R.

2,540
3
24
49

Shawn Pivonka · Answer 2 · 2020-10-14T13:07:04.950

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,
    ]);
}

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?)) — D.R., Jun 09 '20 at 07:57
Damn, I need this `'prompters->collectionA->0->not-working' => 'cant-access',` to work. — Christhofer Natalius, Oct 01 '20 at 11:58
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 — Shawn Pivonka, Oct 02 '20 at 02:10
@ShawnPivonka doesn't work for me, that's why I commented. idk why. I switched to assertEquals instead. — Christhofer Natalius, Oct 02 '20 at 02:40
@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? — Shawn Pivonka, Oct 14 '20 at 13:05

score 2 · Answer 3 · answered Apr 08 '22 at 04:35

2

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,
]);

answered Apr 08 '22 at 04:35

Bedram Tamang

3,748
31
27

Just FYI, `"properties->0->id"=>1,` doesn't work in Laravel 9. Object — does. – D.R. Oct 18 '22 at 11:33
Interesting ! which database you are using? – Bedram Tamang Oct 19 '22 at 02:32
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. – D.R. Oct 19 '22 at 16:37
The above solution is tested on **Postgres** version: 12.x, as mentioned above, not sure about MySQL. – Bedram Tamang Oct 26 '22 at 04:05
not too late to make a switch – D.R. Oct 27 '22 at 06:57

score 1 · Answer 4 · answered May 20 '23 at 13:02

1

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

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

answered May 20 '23 at 13:02

Oleksandr Lukakhin

11
1

score 0 · Answer 5 · answered Jul 26 '21 at 06:56

0

Using json_encode on the value worked for me:

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

answered Jul 26 '21 at 06:56

Gaurav

1,942
19
31

Laravel 5.6. How to test JSON/JSONb columns

5 Answers5

Laravel 7+

1. Object

2. Array