Tutorial - Information Enrich and Omni Search

Building on the foundational features of the housing rental platform discussed in the previous article, we now turn our attention to semi-structured data. This format is ideal for efficiently storing and querying flexible data models, making it easier to manage frequently changing data fields. It is particularly beneficial for handling housing information with diverse attributes. In this article, we explore how to use semi-structured data to enhance property dimensions, providing users with more comprehensive search and evaluation capabilities.

Background

Enriching property information

Detailed listing information is crucial for tenants on housing rental platforms. Each listing includes unique amenities like WiFi, air conditioning, etc., which are often key factors in rental decisions. Traditional table structures may not be flexible enough to manage such varied attributes, but jsonb effectively addresses this issue. This format allows us to store various information in JSON format within a single field, eliminating the need to alter the database structure for each attribute. We can easily add, remove, or update a listing's amenities without changing the database's table structure, simplifying the maintenance of listing information.

Omni property search

Tenants typically search for properties using various criteria, such as price range, property rating, number of bedrooms, and specific amenities. jsonb fields enable the creation of complex search queries, allowing tenants to search based on their preferences. This greatly enhances search efficiency and user experience, helping users quickly find suitable properties.

Detailed property reviews

Evaluating a house is crucial for tenants choosing their accommodation. By analyzing evaluation data stored in the jsonb field, we can improve the display of evaluations. For example, we can highlight satisfaction with specific amenities or the quality of the landlord's service, aspects that traditional table formats struggle to represent effectively.

Incorporating semi-structured data provides our rental system with unmatched flexibility and robust querying functionality. In the upcoming chapters, we will offer a detailed guide on effectively implementing and leveraging this semi-structured data field and demonstrate how to seamlessly integrate these features into our Java application using MyBatis Plus.

Data Model Evolution: Introducing JSONB

To create a dynamic, user-focused platform, adaptability to evolving data and user demands is essential for success. While our rental system already has fundamental functions and structure, we aim to enhance the platform's flexibility and query efficiency by expanding our data model to capture and leverage comprehensive housing information more effectively.

This article introduces two new jsonb fields: amenities and host_verifications_jsonb. These fields reflect our commitment to enhancing and dynamically querying homestay information. By integrating these attributes as semi-structured data, we can offer more personalized and detailed search capabilities while allowing hosts to showcase their listings more richly and flexibly.

The format used in the earlier article on amenities is JSONB. Meanwhile, host_verifications uses an external CSV file imported in TEXT format, which deviates from the standard JSON format. Consequently, the information now expands upon the existing data found in host_verifications_jsonb.

ALTER TABLE listings
    ADD COLUMN host_verifications_jsonb JSONB;
UPDATE listings
SET host_verifications_jsonb = jsonb(REPLACE(REPLACE(host_verifications, '''', '"'), ' ', ''));

Enrich property information

Each listing has distinct features and amenities, which are crucial for helping guests choose their accommodations. The amenities field allows hosts to detail every aspect of their listing, from basics like WiFi and laundry facilities to more unique offerings like a home theater system or private pool.

Omni Search

Finding properties tailored to individual preferences is vital for tenants. The fields will enable platform users to verify landlords' identities and reliability, enhancing trust in the platform. Additionally, this allows us to refine the user's search experience through dynamic queries of these fields.

Adding these new fields will enhance our system's capabilities, allowing us to offer users more personalized and enriched services. In the upcoming chapters, we will explore how to effectively utilize these new data structures and the benefits they will bring to our rental platform.

Omni Search: Dynamic Attribute Search and Data Analysis

Dynamic Amenities Filter

Scenario Description: A user is interested in the location of homes with particular amenities, such as "Wi-Fi" and "Kitchen".

SELECT    l.id,
    l.name,
    l.description,
    l.neighbourhood,
    l.neighbourhood_cleansed
FROM    listings l
WHERE    l.amenities @> '["Wifi", "Kitchen"]' AND
    l.has_availability = TRUE;

Top-Rated Properties

Scenario Description: Users seek listings that exceed a specified rating and include desired amenities.

SELECT    l.id,
    l.name,
    l.review_scores_rating
FROM    listings l
WHERE    l.review_scores_rating > 4.5 AND    l.amenities @> '["Elevator", "Pets allowed"]';

Filter Listings by Host Verification Method

Scenario Description: Certain users may prefer to book listings from fully verified hosts, such as those who must provide a work email and identity verification.

SELECT    l.id,
    l.name,
    l.host_name,
    l.host_verifications
FROM    listings l
WHERE    l.host_verifications_jsonb @> '["work_email"]' AND
    l.host_identity_verified = TRUE;

Detailed Condition Search

Scenario Description: Users can look for properties using various criteria, including price range, availability, room type, and amenities.

SELECT l.id,
       l.name,
       l.price,
       l.room_type,
       l.neighbourhood
FROM listings l
         JOIN calendar c ON l.id = c.listing_id
WHERE c.available = TRUE
  AND l.price BETWEEN 50::MONEY AND 150::MONEY
  AND l.room_type = 'Entire home/apt'
  AND l.amenities @> '["Air conditioning"]'
  AND c.date > '2024-01-01';

Analyzing Keywords in Property Reviews

Scenario Description: Tenants often rely on keywords found in reviews, like "clean" or "spacious," to make their property selection decisions.

SELECT    r.listing_id,
    r.comments
FROM    reviews r
JOIN    listings l ON r.listing_id = l.id
WHERE    r.comments ILIKE '%clean%' AND    r.comments ILIKE '%spacious%' AND    l.amenities ? 'Wifi';

These query scenarios demonstrate the use of jsonb fields to enable more comprehensive searching and data analysis while maintaining the structural features of relational databases, ensuring flexibility and efficiency in data querying and analysis. You can implement these queries in your Java application using the MyBatis Plus connector.

Conclusion

This article highlights the significant potential of jsonb in enhancing property information querying. This semi-structured data type provides exceptional flexibility, enabling the storage and retrieval of complex data structures without compromising performance. By integrating it with traditional relational data, we have created a robust query system that efficiently handles complex and evolving user queries, delivering results at exceptional speed.

We’ve seen how jsonb empowers Tacnode to meet our customers' varied listing needs, whether based on amenities, host verification methods, or specific keywords found in user reviews. These examples represent only the beginning of jsonb's capabilities, showcasing the versatility and importance of the Tacnode database in modern application development.

Tutorial - Information Enrich and Omni Search

On this page