How to query nested json

Good day all!

I'm having difficulties querying a json column. This is what's stored in the field:

[
  {
    "key": "NXtQTr1wa6XCGjfH",
    "layout": "meta_data",
    "attributes": {
      "meta_title": "Testing",
      "meta_robots": "index, follow",
    }
  }
]

This works

$pages = \DB::table('pages')
            ->whereJsonContains('meta_data', ['key' => 'NXtQTr1wa6XCGjfH'])
            ->get();

This doesn't

$pages = \DB::table('pages')
            ->whereJsonContains('meta_data->attributes', ['meta_robots' => 'index, follow'])
            ->get();

it's for sure because of the structure of the json, but can't figure out how to access the nested attributes. Any help is much appreciated.

jlrdw

5 years ago

Level 75

See https://laracasts.com/discuss/channels/eloquent/nested-json-column-where

martinro

5 years ago

Level 4

Nope, unfortunately that doesn't work

kingmaker_bgp

5 years ago

Level 8

I think you have to use the normal array of expected values as the second argument to whereJsonContains

$pages = \DB::table('pages')
            ->whereJsonContains('meta_data->attributes->meta_robots', 'index, follow')
            ->get();

Documentation

martinro

5 years ago

Level 4

Nope, unfortunately that doesn't work

martinro

5 years ago

Best Answer

Level 4

Slept over it and came up with this working:

$pages = \DB::table('pages')
            ->whereJsonContains('meta_data', [['attributes' => ['meta_robots' => 'index, follow']]])
            ->get();

Thanks all for the help!

1 like

Please or to participate in this conversation.