Laravel Query Builder: How to access JSON key/value pair

Using Laravel 5.1, how can I access a JSON key/value pair via Query Builder in a migration? There is JSON data stored in json_data_column that I need to validate in a where() Query Builder expression. Logic is such :

DB::transaction(function () {

 echo 'Deleting rows where user_id is empty...' . PHP_EOL;

 DB::table('payment_methods')->where('json_data_column->user_id', '""')->delete();

});

However, chaining does not seem to work here. Indeed, output is:

  [IlluminateDatabaseQueryException]                                                          
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "user_id" does not exist           
  LINE 1: ... "payment_methods" where "json_data_column"->user_id...                    
                                                               ^ (SQL: delete from "payment_me  
  thods" where "payment_platform_data"->user_id = "")

Is it possible to use chaining using the logic up there in this case? If not, how can I access the JSON fields?

Answer

You need to put user_id in single quotes, or else it is treated like an identifier:

 DB::table('payment_methods')->where("json_data_column->>'user_id'", '')->delete();

Advertisement

Answer