Extracting JSON Info Into Its Own Column? (With Screenshot!)

SQL++
1

Hello! I’m very new to Couchbase and N1QL.

Here is a screenshot for my issue:

Data Sample - Copy - Copy
Data Sample - Copy - Copy1773×416 63.3 KB

  • Query result returns custom_params column, which has various sub-columns
  • How would I transform “# shifts this ship” into a proper column - such as country or city?

I looked through the N1QL language reference, but didn’t find an obvious answer - so thought I’d ask!

Edit: I wanted to mention specifically that currently, the data I’m trying to work with has a very poor naming convention. Notice that one of the sub columns is listed as “# shifts this ship” and not something like “NumShiftsThisShip”. This is where my problem is arising.

2

Input data is JSON, Output also JOSN. JSON has nested objects and Arrays. Converting that to flatten columns is not easy.
You should look JSON tab too view results vs Table view.

If you really needs column write select each nested field as separate projection

SELECT d.appid, d.city, d.coutry, d.custom_params.*
FROM default AS d
WHERE ....

 SELECT d.appid, d.city, d.coutry, d.custom_params.`# shifts this ship` AS NumShiftsThisShip
    FROM default AS d
    WHERE ....

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/identifiers.html#escaped-identifiers