• Query Array of Objects in Postgres

    Storing data in a JSON column in Postgres can be very handy but a bit more difficult to query than normal tables. In particular, querying arrays of objects had me stumped for a while.

    The trick is to use jsonb_array_elements to expand the array into a row for every object. Then each object can be queried individually by using the ->> operator extract a key and use it in a where clause.

    Conceptually this isn’t much different than a one to many inner join where, because of the join, you can have a row mostly duplicated in the query output after it is joined.

    For example:

    User Table Address Table
    Full Name Phone Zip
    John Doe 555-555-5555 78701
    John Doe 555-555-5555 78613

    So let’s say you have a user address and have an addresses which is a JSONB column, containing multiple address objects.

    The following query expands the addresses into multiple rows, then uses the ->> operator to extract the zip field and then finds any that are equal to 78701.

    select *
    from "user" u, jsonb_array_elements(u.addresses) as obj
    where  obj->>'zip' = '78701';

    Just like with an inner join there might be a need to do a group by to get the result you want but overall it is pretty straightforward!

    Read more about the the available JSON operators and functions here.

  • Interactive Git Stash

    Often times I have the need to stash some changes in git but don’t want to stash everything.

    The solution is to use git stash -p. The -p instructs git to do a stash interactively and will iterate over each changed hunk one at a time and instruct git what to do.

    These are the options:

    y - stash this hunk
    n - do not stash this hunk
    q - quit; do not stash this hunk or any of the remaining ones
    a - stash this hunk and all later hunks in the file
    d - do not stash this hunk or any of the later hunks in the file
    g - select a hunk to go to
    / - search for a hunk matching the given regex
    j - leave this hunk undecided, see next undecided hunk
    J - leave this hunk undecided, see next hunk
    k - leave this hunk undecided, see previous undecided hunk
    K - leave this hunk undecided, see previous hunk
    s - split the current hunk into smaller hunks
    e - manually edit the current hunk
    ? - print help
  • Purge Redis Keys with Lua Script

    Redis has some powerful Lua scripting capabilities. One of the uses I’ve found for this feature is purging cache keys. On occasion I need to purge a set of keys that all have the same prefix.

    The following command will do just that.

    EVAL "return redis.call('del', unpack(redis.call('keys', ARGV[1])))" 0 prefix:*

    Replace prefix with whatever prefix you are looking for and they will be deleted from Redis.