Get JSON array as object with PHP and PDO

[Thallius]

Hi,

I need to work on an older script with PHP and PDO.

I have a mySQL database containing a JSON column named “types” which contains a simple array like

["a","b","c"]

Lets imagine the table has another column named “Name”.

I now want to fetch from this table an object with PDO which is containing a nested array from the JSON column.

So the result of the

result = $stmt->fetch(PDO::FETCH_OBJ)

looks like that at the moment

object(stdClass)#129 (16) {
  ["name"]=>
  string(9) "theName"
  ["types"]=>
  string(1) "["a","b","c"]"
}

But I want to directly parse the JSON to an object that the result is

object(stdClass)#129 (16) {
  ["name"]=>
  string(9) "theName"
  ["types"]=>
  array(3) {
    [0]=>
    string(3) "a"
    [1]=>
    string(3) "b"
    [2]=>
    string(3) "c"
  }
}

Is this possible or do I need to run a json_decode over each fetched item?

In node this is pretty easy by putting a callback in the mySQL result parse, but no idea how this could work with PDO

[m_hutley]

@Thallius

mySQL database containing a JSON column named “types” which contains a simple array

Ew.

@Thallius

But I want to directly parse the JSON to an object

Well, there’s two different paths here; parsing a JSON, and parsing specifically this array-string. I shall assume the latter for the moment.
$result->types = explode(",",str_replace(array("[","]",'"'), "",$result->types));

Can you do it “directly”? no. PDO doesnt have a column type for JSON.

[Thallius]

@m_hutley

Well, there’s two different paths here; parsing a JSON, and parsing specifically this array-string. I shall assume the latter for the moment.
$result->types = explode(",",str_replace(array("[","]",'"'), "",$result->types));

I guess this is much easier by doing a

$result->types = json_decode($result->types);

Can you do it “directly”? no. PDO doesnt have a column type for JSON.

That’s what I was afraid of. Really bad as this is today a more then common situation where you have JSON in a database table.

[m_hutley]

@Thallius

a more then common situation where you have JSON in a database table.

I can hear @r937 's screams of anguish from miles away…

[Thallius]

@m_hutley

I can hear @r937 's screams of anguish from miles away…

I am also not a big fan, but there are situations where it makes perfectly sense. For example for settings with a multi select. You can of course create another table for each option and then create a cross reference table for the selected once, but as this values are rarely updated and never searched for it is not needed. Here you put them in an JSON array in one column and you are fine by fetching them once for your user and that’s it.

[r937]

@m_hutley

I can hear @r937 's screams of anguish from miles away…

no, i can understand the attraction for this format – it’s easy to display the data

but don’t you ever dare to search for a value inside a JSON block – muy despacio

[Thallius]

@r937

but don’t you ever dare to search for a value inside a JSON block – muy despacio

Searching in a json block is super fast. I didn’t believed it also, but I have seen some benchmarks and you can find a value of one attribute in a JSON object as fast as if you search for a string in a specific column. Of course only if you have no index on any column. You can also create a column always containing the value of one attribute of the JSON object and then add an index on it. Then the database is able to search with index in JSON.

It is amazing what they have developed. But as I said. I am not a big fan either.