Skip to content

Instantly share code, notes, and snippets.

@ercanertan

ercanertan/JSON_Query.txt

Last active July 14, 2020 08:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ercanertan/a2a7724fc5a58469d042cb8de11d8b48 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ercanertan/a2a7724fc5a58469d042cb8de11d8b48 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ercanertan revised this gist Jul 14, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion JSON_Query.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    //Compilete Sample
    // Sample Query

    $page = Page::query()
    ->select('title',\DB::raw('advance_custom_fields->"$[*].field_type" AS field'))
  2. ercanertan renamed this gist Jul 14, 2020. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → JSON_Query.txt
    View file
    File renamed without changes.
  3. ercanertan created this gist Jul 14, 2020.
    66 changes: 66 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,66 @@
    //Compilete Sample

    $page = Page::query()
    ->select('title',\DB::raw('advance_custom_fields->"$[*].field_type" AS field'))
    ->whereRaw('advance_custom_fields->"$[*].field_type" IS NOT NULL')
    ->whereRaw('JSON_SEARCH(advance_custom_fields, "one", "image") IS NOT NULL')
    ->get();

    Table is pages
    json coloumn is advance_custom_fields

    Example json
    [
    {
    "name": "",
    "style": "col-md-4 order-first",
    "attributes": {
    "val_1": "aa",
    "val_2": "dd",
    "bg_color": "",
    "field-css": "col-md-4 order-first",
    "border_color": ""
    },
    "field_type": "image"
    },
    {
    "name": "",
    "style": "",
    "attributes": {
    "val_1": [
    "/uploads/1/A.png",
    "/uploads/2/B.png"
    ],
    "bg_color": "",
    "field-css": "",
    "border_color": ""
    },
    "field_type": "carousel"
    }
    ]


    $[*].field_type
    $ -> json
    [*] -> array
    field_type -> key




    //Select
    ->select('title', \DB::raw('advance_custom_fields->"$[*].field_type" AS field'))

    or

    removing double quote from result with 'JSON_UNQUOTE'
    ->select('title', \DB::raw('JSON_UNQUOTE(advance_custom_fields->"$[*].field_type") AS field'))

    removing double quote from result short version with '->>'
    ->select('title', \DB::raw('advance_custom_fields->>"$[*].field_type" AS field'))

    //whereRaw
    ->whereRaw('advance_custom_fields->"$[*].field_type" IS NOT NULL')
    or
    ->whereRaw('JSON_SEARCH(advance_custom_fields, "all", "image") IS NOT NULL')