Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Query JSON Using SQL With Couchbase Query Workbench

DZone's Guide to

Query JSON Using SQL With Couchbase Query Workbench

Would you like to query a JSON document database using SQL-like syntax? Couchbase has N1QL for you. Read on to learn more.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

You’d like to query a JSON document database using SQL-like syntax?

Couchbase has N1QL for you.

N1QL is a declarative query language that extends SQL for JSON. You can query data via native framework and language integration, a fluent API, or the JDBC/ODBC drivers.

N1QL enables you to query JSON documents without any limitations–sort, filter, transform, group, and combine data with a single query. That’s right. You can combine data from multiple documents with a JOIN. That flexible data model you were promised? This is it. You’re no longer limited to “single table” and “table per query” data models.

N1QL Tutorial is a great resource to learn the concepts of querying JSON documents using SQL-like syntax. Here are some examples:

SELECT children[0].fname AS cname
    FROM tutorial
       WHERE fname='Dave'

OR

SELECT fname, age, age/7 AS age_dog_years 
    FROM tutorial 
        WHERE fname = 'Dave'

OR

SELECT relation, COUNT(*) AS count
    FROM tutorial
        GROUP BY relation
            HAVING COUNT(*) > 1

OR

SELECT t.relation, COUNT(*) AS count, AVG(c.age) AS avg_age
    FROM tutorial t
    UNNEST t.children c
    WHERE c.age > 10
    GROUP BY t.relation
    HAVING COUNT(*) > 1
    ORDER BY avg_age DESC
    LIMIT 1 OFFSET 1

OR

SELECT
product.name, 
COUNT(reviews) AS reviewCount,
ROUND(AVG(reviews.rating),1) AS AvgRating,
category 
 FROM reviews AS reviews
JOIN product AS product 
 ON KEYS reviews.productId
UNNEST product.categories AS category
WHERE category = "Appliances"
GROUP BY category, product
ORDER BY AvgRating 
DESC LIMIT 3

So, are you ready to experiment with N1QL? You can certainly use CBQ tool.

Alternatively, you can use Couchbase Query Workbench. The query workbench provides a rich graphical user interface to prepare and execute simple to complex N1QL queries. It provides a convenient way to perform query development by enabling you to browse, create, and run N1QL statements, and view results.

Want to learn how to get started?

Learn all about it in this brief session with Eben Haber:


Ask your questions at Stack Overflow or Couchbase Forums.

You can also follow us at @couchbasedev and @couchbase.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
couchbase ,json ,sql ,tutorial

Published at DZone with permission of Arun Gupta, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}