Index For Finding an Element in a Json Array

Index for finding an element in a JSON array

jsonb in Postgres 9.4+

The binary JSON data type jsonb largely improves index options. You can now have a GIN index on a jsonb array directly:

CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);

No need for a function to convert the array. This would support a query:

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';

@> being the jsonb "contains" operator, which can use the GIN index. (Not for json, only jsonb!)

Or you use the more specialized, non-default GIN operator class jsonb_path_ops for the index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !

Same query.

Currently jsonb_path_ops only supports the @> operator. But it's typically much smaller and faster. There are more index options, details in the manual.

If the column artists only holds names as displayed in the example, it would be more efficient to store just the values as JSON text primitives and the redundant key can be the column name.

Note the difference between JSON objects and primitive types:

• Using indexes in json array in PostgreSQL

CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks  VALUES (2, '["The Dirty Heads", "Louis Richards"]');

CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);

Query:

SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';

? does not work for object values, just keys and array elements.

Or:

CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING  gin (artistnames jsonb_path_ops);

Query:

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;

More efficient if names are highly duplicative.

json in Postgres 9.3+

This should work with an IMMUTABLE function:

CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';

Create this functional index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

And use a query like this. The expression in the WHERE clause has to match the one in the index:

SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));

Updated with feedback in comments. We need to use array operators to support the GIN index.

The "is contained by" operator <@ in this case.

Notes on function volatility

You can declare your function IMMUTABLE even if json_array_elements() isn't wasn't.

Most JSON functions used to be only STABLE, not IMMUTABLE. There was a discussion on the hackers list to change that. Most are IMMUTABLE now. Check with:

SELECT p.proname, p.provolatile
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'pg_catalog'
AND    p.proname ~~* '%json%';

Functional indexes only work with IMMUTABLE functions.

What is the best way of getting index from huge JSON Array of objects that matches with array of JSON objects

You can use Map Object for this.

let theMap = new Map(hugeArray.map((item, index) => [item.firstName + " " + item.lastName, index]));

let result = matchArray.map(item => theMap.get(item.firstName + " " + item.lastName))
  .filter(i => i !== undefined)

This assumes firstName or lastName do not have spaces. Use another char if they might include spaces.

If hugeArray has duplicates:

let theMap = new Map();
hugeArray.forEach((item, index) => {
  let key = item.firstName + " " + item.lastName;
  let value = theMap.get(key);
  if (value === undefined)
    theMap.set(key, [index]);
  else
    value.push(index);
});

let result = matchArray.flatMap(item => theMap.get(item.firstName + " " + item.lastName))
  .filter(i => i !== undefined);

If we need a sorted result when hugeArray has duplicates:

let result = matchArray.flatMap(item => theMap.get(item.firstName + " " + item.lastName))
      .filter(i => i !== undefined).sort();

How to fetch the entire JSON array based on index using simple JSON Library

You can simply write System.out.println(object);. JSONObject's toString() method will automatically convert it to json. Your for loop can be modified as

for (int i = 0; i < array.size(); i++) {
    index++;
    JSONObject object = (JSONObject) array.get(i);
    System.out.println(object);
}

How can I get the index from a JSON object with value?

You will have to use Array.find or Array.filter or Array.forEach.

Since your value is array and you need the position of the element, you will have to iterate over it.

Array.find

var data = [{"name":"placeHolder","section":"right"},{"name":"Overview","section":"left"},{"name":"ByFunction","section":"left"},{"name":"Time","section":"left"},{"name":"allFit","section":"left"},{"name":"allbMatches","section":"left"},{"name":"allOffers","section":"left"},{"name":"allInterests","section":"left"},{"name":"allResponses","section":"left"},{"name":"divChanged","section":"right"}];
var index = -1;
var val = "allInterests"
var filteredObj = data.find(function(item, i){
  if(item.name === val){
    index = i;
    return i;
  }
});

console.log(index, filteredObj);

i = 0
for person in json["users"]:
    if "person2" in person:
        print(i)
    i += 1

JSONArray event_values = opoutput.getJSONArray("DISPLAY_VALUES");
JSONArray event_codes = opoutput.getJSONArray("VALUES");

List<String> valueList = new ArrayList<String>();
List<String> displayList = new ArrayList<String>();
for(int i=0;i<event_codes.length();i++){
        // if both event_values and event_codes are of equal length
        valueList.add(event_codes.getString(i));
        displayList.add(event_values.getString(i));
    }

int index = valueList.indexOf("ACCPT");
String valueToDisplay = displayList.get(index);

const data = [{
  "successful":[
   {"id":"uppy-a8geo/fw/400x400/jpg-1d-2v-1e-image/jpeg-10097-1626922525568"},
   {"id":"uppy-maxresdefault/jpg-1e-image/jpeg-81700-1626406845772"}
  ],
  "failed":[],
  "uploadID":"ckss6e4xv00023h63uov94n5e"
 }];
 
const toRemove = "uppy-maxresdefault/jpg-1e-image/jpeg-81700-1626406845772"; 
data.forEach(item => {
  Object.values(item).forEach(array => {
    if (!Array.isArray(array))
        return;
    const index = array.findIndex(elm => elm.id === toRemove);
    if (index > -1)
      array.splice(index, 1);
  });
});

console.log(data);