Active Storage：如何基于Blob的Metadata字段查询特定年份记录

Since your metadata field is stored as text (containing serialized JSON data), you’ll need to use database-specific JSON functions to query the embedded image_taken_time value. Here’s how to handle this based on your database system:

First, Confirm the Data Format #

Your analyzer stores image_taken_time as a DateTime object pulled from EXIF, which gets serialized to an ISO 8601 string in the metadata (e.g., "2023-08-15T14:30:00+02:00"). We’ll use this string format for our queries.

1. PostgreSQL Queries #

PostgreSQL has robust native JSON support, so we can cast the text metadata to jsonb and extract values directly:

Basic Year Match (LIKE) #

A quick, simple approach if you don’t need strict timestamp validation:

# Find all blobs captured in 2023
ActiveStorage::Blob.where("metadata::jsonb->>'image_taken_time' LIKE ?", '2023%')

Precise Year Extraction #

For stricter checks (avoids partial matches like 20230), extract the year from the parsed timestamp:

ActiveStorage::Blob.where(
  "EXTRACT(YEAR FROM (metadata::jsonb->>'image_taken_time')::timestamp) = ?",
  2023
)

Filter for Blobs With Valid Metadata #

Add a check to exclude blobs missing image_taken_time (e.g., images without EXIF data):

ActiveStorage::Blob
  .where("metadata::jsonb ? 'image_taken_time'")
  .where(
    "EXTRACT(YEAR FROM (metadata::jsonb->>'image_taken_time')::timestamp) = ?",
    2023
  )

2. MySQL Queries #

MySQL 5.7+ supports JSON functions to extract and parse the metadata:

Basic Year Match (LIKE) #

ActiveStorage::Blob.where(
  "JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.image_taken_time')) LIKE ?",
  '2023%'
)
# Or use the shorthand ->> operator (MySQL 5.7+)
ActiveStorage::Blob.where("metadata->>'$.image_taken_time' LIKE ?", '2023%')

Precise Year Extraction #

Parse the timestamp string and extract the year explicitly:

ActiveStorage::Blob.where(
  "YEAR(STR_TO_DATE(metadata->>'$.image_taken_time', '%Y-%m-%dT%H:%i:%s')) = ?",
  2023
)

Filter for Valid Metadata #

Exclude blobs missing the image_taken_time field:

ActiveStorage::Blob
  .where("JSON_CONTAINS_PATH(metadata, 'one', '$.image_taken_time')")
  .where(
    "YEAR(STR_TO_DATE(metadata->>'$.image_taken_time', '%Y-%m-%dT%H:%i:%s')) = ?",
    2023
  )

3. Reusable Cross-Database Scope #

To simplify repeated queries, add a scope to ActiveStorage::Blob. Create an initializer (e.g., config/initializers/active_storage_metadata_queries.rb):

Rails.application.config.to_prepare do
  ActiveStorage::Blob.class_eval do
    scope :taken_in_year, ->(year) {
      adapter = ActiveRecord::Base.connection.adapter_name.downcase

      case adapter
      when 'postgresql'
        where("metadata::jsonb ? 'image_taken_time'")
          .where(
            "EXTRACT(YEAR FROM (metadata::jsonb->>'image_taken_time')::timestamp) = ?",
            year
          )
      when /mysql/
        where("JSON_CONTAINS_PATH(metadata, 'one', '$.image_taken_time')")
          .where(
            "YEAR(STR_TO_DATE(metadata->>'$.image_taken_time', '%Y-%m-%dT%H:%i:%s')) = ?",
            year
          )
      when 'sqlite'
        # SQLite handling with strftime for year extraction
        where("json_extract(metadata, '$.image_taken_time') IS NOT NULL")
          .where("strftime('%Y', json_extract(metadata, '$.image_taken_time')) = ?", year.to_s)
      end
    }
  end
end

Now you can use it anywhere in your app with a clean interface:

# Get all blobs captured in 2022
ActiveStorage::Blob.taken_in_year(2022)

Key Notes #

• Timezone Awareness: EXIF’s DateTimeOriginal may not include timezone data. If your app uses UTC, ensure you convert the timestamp to UTC during analysis to avoid year mismatches (e.g., a photo taken on Dec 31st in a timezone ahead of UTC might register as the next year in UTC).
• Performance: For frequent queries, consider adding a generated column for the capture year (database-specific) to avoid parsing JSON on every lookup.

内容的提问来源于stack exchange，提问作者Ankita Agrawal