How to extract value from a generic JSON doc

N1QL
1

I have a json structure which has the following structure, the fields has list of names and values (sort of key value pair). How do I extract the value for a specific field where name = ‘abc’, I would expect the value of output to return as “answer1”. Appreciate your help !

    {
        "type": "sample_test_record",
        "fields": [
          {
            "name": "abc",
            "val": "answer1"
          },
          {
            "name": "xyz",
            "val": "answer2"
          }
        ]
      }
2

You can unnest to achieve this:

SELECT f.val
FROM the_bucket b 
UNNEST b.fields f 
WHERE f.name = "abc" 
AND b.type = "sample_test_record"

(Ref: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/unnest.html )

HTH.

2 Likes
3 
SELECT FIRST v.val FOR v IN d.fields WHEN v.name = "abc" END
FROM default AS d WHERE ..........;

OR

Use @dh query with following index (CB 7.0+)

CREATE INDEX ix1 ON the_bucket ( ALL ARRAY FLATTEN_KEYS(f.name,f.val) FOR f IN fields END) WHERE type = "sample_test_record";

1 Like
4

Thank you exactly what I was looking for