N1QL query for deleting duplicate records in JSONArray Document

SQL++
1

I am trying to create N1QL Query to delete the JsonArraydocuments which are duplicated. Any help will be appreciated pls.

Sample:
Bucket name: “Apple”
Records format as follows
Document id = 1111
JsonArrrayContent is:
[{price:2111, type:“simla”, “color”:red, “time”: 01-Apr},
{price:2111, type:“simla”, “color”:red, “time”: 02-Apr} ]

I want to create a N1QL query so that, the above JSON array document shud become as follows

[price:2111, type:“simla”, “color”:red, “time”: 02-Apr ]

You can see that only the latest Array by time 02-Apr gets stored and the first one gets deleted.
How can i achieve this. It is very urgent.

2

Store the time ISO-8601 format https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

Once You have that. Assume ja is Jasson Array
Adjust GROUP BY on what fields you want latest (ex. you want one for color,type).

SELECT d.*,  (SELECT  RAW MAX([j.time,j])[1] FROM d.ja AS j  GROUP BY j.type, j.color ) AS ja
FROM default AS d
WHERE  .....;

UPDATE default AS d
SET d.ja = (SELECT  RAW MAX([j.time,j])[1] FROM d.ja AS j  GROUP BY j.type, j.color )
WHERE ...
3

Hi @vsr1 thanks for quick response.
It is not working. The SELECT query returns empty response,

Actually this is my input JSONArray Document stored in couchbase
[
{
“field0”: “0”,
“field1”: “aaaaaa”,
“field2”: “bbbbbb”,
“field3”: “cccccc”,
“field4”: 1111111,
“field5”: “dddddd”
},
{
“field0”: “0”,
“field1”: “aaaaaa”,
“field2”: “bbbbbb”,
“field3”: “cccccc”,
“field4”: 2222222,
“field5”: “dddddd”
}
]

the field4 is stored as long type (which you can see in the above JSON). All other fields are string type.

So now when i run a N1QL query it should return and update the JSON Array document which has maximum “field4” value.
in this case it should return the below and update the document
[
{
“field0”: “0”,
“field1”: “aaaaaa”,
“field2”: “bbbbbb”,
“field3”: “cccccc”,
“field4”: 2222222,
“field5”: “dddddd”
}
]

Any clue/help

4

It should work…

SELECT (SELECT  RAW MAX([j.field4,j])[1] FROM d.ja AS j) AS ja
LET d = { "ja": [ { "field0": "0", "field1": "aaaaaa", "field2": "bbbbbb", "field3": "cccccc", "field4": 1111111, "field5": "dddddd" }, { "field0": "0", "field1": "aaaaaa", "field2": "bbbbbb", "field3": "cccccc", "field4": 2222222, "field5": "dddddd" } ] };

 "results": [
    {
        "ja": [
            {
                "field0": "0",
                "field1": "aaaaaa",
                "field2": "bbbbbb",
                "field3": "cccccc",
                "field4": 2222222,
                "field5": "dddddd"
            }
        ]
    }
    ]
5

@vsr1 it works good now when i apply to single data or JSONArrayDocument

But somehow when i apply the N1QL you have provided, into my real couchbase data its not working.

My Couchbase records storage look exactly similar like this.
Example: Bucket-Name: test-bucket

  1. My first record is
    Key: 1111
    Document Value is:
    [
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 1111111,
    “field5”: “dddddd”
    },
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 2222222,
    “field5”: “dddddd”
    }
    ]
  2. second record is
    Key: 2222
    Document Value is:
    [
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 3333,
    “field5”: “dddddd”
    },
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 4444,
    “field5”: “dddddd”
    }
    ]

So when i execute the N1QL it should be executed in all the JSONDocuments/records that are stored in the couchbase and finally my couchbase records should like this:

  1. First Record - After the N1QL query should look like
    Key: 1111
    Document Value is:
    [
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 2222222,
    “field5”: “dddddd”
    }
    ]

  2. Second Record - After the N1QL query should look like
    Key: 2222
    Document Value is:
    [
    {
    “field0”: “0”,
    “field1”: “aaaaaa”,
    “field2”: “bbbbbb”,
    “field3”: “cccccc”,
    “field4”: 4444,
    “field5”: “dddddd”
    }
    ]

and so on for all the records.

Any suggestions on where am i making mistake in the N1QL query?

6 
SELECT META(d).id, (SELECT RAW MAX([j.field4,j])[1] FROM d AS j) AS val
FROM  `test-bucket` AS d;
7

@vsr thats great. it works cool

The Select query is working great. But If i want to update the bucket with this new select query data, it not work and it errors out.

This is the query, i am trying

UPDATE test-bucket AS d SET d = SELECT META(d).id, (SELECT RAW MAX([j.field4,j])[1] FROM d AS j) AS val FROM test-bucket AS d;

8 
UPDATE `test-bucket` AS d 
SET d = (SELECT RAW MAX([j.field4,j])[1] FROM d AS j) ;
9

@vsr1
Many Thanks.

I have one more doubt . in the below N1QL query,

SELECT RAW (SELECT RAW MAX([j.field4,j])[1] FROM d AS j GROUP BY j.field2)
FROM test-bucket AS d

it retrieves results like this:
[
[
{
“field1”: “0000000”,
“field2”: aaaaa,
“field3”: bbbbbb,
“field4”: 111111,
“field5”: “qqqqq”,
},
{
“field1”: “00000000”,
“field2”: aaaa,
“field3”: bbbbb,
“field4”: 222222,
“field5”: “qqqqq”,
}
]
]

i want to be make it like this =>
[
{
“field1”: “0000000”,
“field2”: aaaaa,
“field3”: bbbbbb,
“field4”: 111111,
“field5”: “qqqqq”,
},
{
“field1”: “00000000”,
“field2”: aaaa,
“field3”: bbbbb,
“field4”: 222222,
“field5”: “qqqqq”,
}
]

basically i dont want the outer-most square bracket in my result.
any way to get rid of that square bracket?

10 
SELECT MAX([u.field4,u])[1].*
FROM `test-bucket` AS d
UNNEST d AS u
GROUP BY u.field2;
11

@vsr you killed it :slight_smile: :slightly_smiling_face:
it works cool
Thanks for quick response and help