PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Parsing large JSON File being used as data for postgres database

I have a large (~65,000 lines) JSON file which is being used as data for a prisma postgres database in a Next.js project I’m working on. The file contains entries like so:

[
  {
    "NativeClass": "class-name",
    "Classes": [ // data is derived from attributes in this class
      {
        "key1": "val1",
        "key2": "val2",
        ...
      },
      {
        ...
      }
    ]
  },
  {
    "NativeClass": "class-name2",
    "Classes": [ // this class may be skipped
    ...
  }
]

I only need the data from "Classes" after certain "class names", the rest would be discarded.

My question has two parts:

  1. What would be the best way to go about parsing the JSON file into SQL statements to populate the database?

I’m not very familiar with typescript, so I’ve been toying with the idea of parsing the file in python first, but I’m unsure how I would then insert the data into the database.

The other option I’ve been thinking about is using the JSON.parse function in typescript, but i have been struggling with it.
So far i’ve tried:

  type Ingredients = {
    ingredientName: String;
    description: String;
    stackSize: number;
    sinkPoints: number;
    energyValue: number;
    radioactiveDecay: number;
    form: String;
    fluidColor: String;
  };

  type Recipes = {
    recipeName: String;
    alternateRecipe: Boolean;
    craftedIn: String;
    inCraftBench: Boolean;
    inWorkshop: Boolean;
    craftingTime: number;
    craftingClicks: number;
  };

  let ingredients : Ingredients[] = [];
  let recipes : Recipes[] = [];

  var needNext = false;

  const file = await fs.readFile(process.cwd() + '/lib/parse/Docs.json', 'utf8');
  const data = JSON.parse(file, function(key, value) {
    if (key == "NativeClass" && value == "/Script/CoreUObject.Class'/Script/FactoryGame.FGItemDescriptor'") { 
      needNext = true; // target class reached
    }
    if (key == "Classes" && needNext) {
      needNext = false;
      // need data from this attribute
    }
  });

but I get an error related to undefined values. I understand that when using a reviver the inner children are transformed before the parent, so I dont know how I would be able to know when to extract the data when I need to know the name of the class (parent) before I reach the children.
So, is there a better way to do this or am I just not understanding how the parse method works?

  1. The second part to my question has to do with the flow of parsing this JSON file in relation to updates of the file.

This is just a side project of mine that I’m doing to try and teach myself web development as I’m a Software Dev major college student right now.
As a result, I would like to know "best practices" as to how this sort of situation would be handled. The JSON file would rarely be changed, but when it does I would want to re-evaluate the JSON so I can update the database as needed.

Apologies if my knowledge is lacking, I did loads of research before I came to post this question but I’ve been unable to find solid information.

Thank you for any help.

Tags:

2

Answers


  1. 0

    What about something like this using JSON functions:

    SELECT
    x ->> 'NativeClass' AS NativeClass,
    json_array_elements((x ->> 'Classes')::json) ->> 'key1' AS key1,
    json_array_elements((x ->> 'Classes')::json) ->> 'key2' AS key2
FROM
    json_array_elements('
[
  {
    "NativeClass": "foobar",
    "Classes": [ 
      {
        "key1": "val1",
        "key2": "val2"
      },
      {
        "key1": "val1",
        "key2": "val2"
      }
    ]
  },
  {
    "NativeClass": "xpto",
    "Classes": [ 
      {
        "key1": "val1",
        "key2": "val2"
      },
      {
        "key1": "val1",
        "key2": "val2"
      }
    ]
    }
]') AS x

    Output:

     nativeclass | key1 | key2 
-------------+------+------
 foobar      | val1 | val2
 foobar      | val1 | val2
 xpto        | val1 | val2
 xpto        | val1 | val2
(4 rows)

    SQLFiddle

    Login or Signup to reply.
  2. 0

    An assumption: your code lists Ingredients and Recipes types, but I assume they are not in any way relevant here.

    You probably haven’t worked a lot with JSON and are therefore over-complicating things. A simplistic approach would be:

    // Open the file and parse it as you do in your sample.
// Using an example structure here:

const json = [
  {
    "NativeClass": "class-name",
    "Classes": [
      {
        "key1": "val1",
        "key2": "val2",
      },
      {
        "key3": "val3",
        "key4": "val4",
      },
    ]
  },
  {
    "NativeClass": "class-name2",
    "Classes": [
      {
        "key5": "val5",
      },
    ]
  }
];

// Filter records which have the targeted value
const values = json
  .filter(item => item.NativeClass === "class-name")
  .flatMap(item => item.Classes);

console.log(values);
// ^ [{ key1: 'val1', key2: 'val2' }, { key3: 'val3', key4: 'val4' }]

// Insert the values into DB any way you wish.
// You may have to do further mutating of data depending on your table structure:
const insertedRecords = await prisma.table.createMany({
  data: values,
});

    There really shouldn’t be anything more to it.

    As for the second part of your question relating to how to update this data. You leave out what this data is used for, but I assume it is some kind of data seeding. There are several options depending on your exact needs, but I would:

    1. add an npm script:
    "prisma": {
  "seed": "ts-node prisma/seed.ts"
}
    1. delete old data, I assume, and insert the changed data:
    /* prisma/seed.ts */

await prisma.table.deleteMany();

// parse JSON...

// Insert:
await prisma.table.createMany({
  data: values,
});

    Prisma has good documentation on how seeding works with plenty of examples, so check it out.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search