CODEWITHSUNDEEP
mysqlsqlyaml
skinkelynet
skinkelynet

Reputation: 977

Query on YAML in MySQL

Say I have a table "Customers", which has a TEXT column "preferences" in which I store YAML. The YAML is a Hash/Dictionary/Map with attributes like show_email, likes_to_party and last_changed_prefs_at. Is there any way in MySQL to query on this YAML data to e.g. return all customers who has customers.preferences["likes_to_party"] == true?

It gets more complicated though, as I actually have a serialized date field which I'd like to know whether it belongs to some date interval: customers.preferences["last_changed_prefs_at"].between(some_date, some_other_date) == true

I'm initially thinking regex.. but that's pretty hacky.

Upvotes: 4

Views: 5201

Answers (1)

skinkelynet
skinkelynet

Reputation: 977

By using SUBSTRING(), LOCATE() and UNIX_TIMESTAMP() I was able to construct a rather ugly hack:

UNIX_TIMESTAMP(
  SUBSTRING(customers.preferences, 
    LOCATE(\"last_changed_prefs_at\", cusstomers.preferences) + 22, 29)
) BETWEEN UNIX_TIMESTAMP(:period_start) AND UNIX_TIMESTAMP(:period_end)

Upvotes: 4

Related Questions