Select specific fields from an array

SQL++
1

I wanted to select specific columns from an array.

Document:
[
{
“brandId”: 1,
“category”: “Shopping”,
“type”: “Brand”,
“images”: [
{
“imageId”: 17,
“location”: “ca8aaa950051.png”,
“target”: “MOBILE”,
“type”: “BrandImage”
}
]
}
]

N1QL:
SELECT brandId, category, images
FROM bucket_name WHERE type=‘Brand’;

I don’t want the field “type” within images. How can i achieve this ?
Whatever the value of type (within images), i don’t want this field.

2

You’ll have to have the whole array in a field… I’ve used brandinfo here.

SELECT b.brandId, b.category
FROM yourbucket UNNEST brandinfo b
WHERE b.type = 'Brand'
3

Thanks keshav.

But unnest modifies the structure of images from an array.

I expect the result to be the same document (with the same structure), except the ‘type’ field within images array.

Result expected:
[
{
“brandId”: 1,
“category”: “Shopping”,
“images”: [
{
“imageId”: 17,
“location”: “ca8aaa950051.png”,
“target”: “MOBILE”
}
]
}
]

4

Hi @g.radhakrishnan,
can you try:
SELECT brandId, category, ARRAY object_remove(x, “type”) FOR x IN images END AS images
FROM bucket_name WHERE type=‘Brand’;

5

That’s great… Thanks prasad.

Can you please suggest how to remove/exclude multiple fields?
Say ‘target’ in addition to ‘type’.

SELECT brandId, category, 
ARRAY object_remove(x, "type") FOR x IN images END AS images
FROM bucket_name 
WHERE type='Brand';
6

Use object_remove() in a nested fashion:

SELECT brandId, category, ARRAY OBJect_remove(OBJect_remove(x, "type"), "target") FOR x IN images END as images
FROM bucket_name WHERE type='Brand';
7

Thanks very much Prasad.

8

I have one more question.
When i use object_remove for a field in the first layer of JSON, i get the result with $1. How to get the result without this $1 ?

N1QL:
SELECT offer_catalogue_dev.* FROM sample_bucket WHERE type='Outlet';
Result:
[
  {
    "airport": "Changi Airport T1",
    "externalId": "extId001",
    "outletId": 1,
    "partnershipId": "1",
    "type": "Outlet"
  },
  {
    "airport": "Changi Airport T2",
    "externalId": "extId002",
    "outletId": 2,
    "partnershipId": "2",
    "type": "Outlet"
  }
]

N1QL:
SELECT object_remove(offer_catalogue_dev, 'type') FROM sample_bucket WHERE type='Outlet';
Result:
[
  {
	"$1": {
		"airport": "Changi Airport T1",
		"externalId": "extId001",
		"outletId": 1,
		"partnershipId": "1"
	}
  },
  {
	"$1": {
		"airport": "Changi Airport T2",
		"externalId": "extId002",
		"outletId": 2,
		"partnershipId": "2"
	}
  }
]
9

Try this (similar to your first n1ql statement):
SELECT object_remove(offer_catalogue_dev, ‘type’).* FROM sample_bucket WHERE type=‘Outlet’;

10

try this

SELECT [brandId, category, images] FROM bucket_name WHERE type=‘Brand’;

11

Instead of using OBJECT_REMOVE, you can use OBJECT_CONCAT to select only the fields you want. Running a single operation is more performant and more readable than nesting OBJECT_REMOVE functions.