How to filter and get specific keys from JSON objects array

I have a JSONB field containing a value in this structure:

[     {         "type": "small",         "name": "Josh",         "greeting": "Hello buddy!",         "hobby": "billiards",         "place": "Barcelona"     },     {         "type": "big",         "name": "Aziz",         "greeting": "Hey man!",         "hobby": "tennis",         "place": "Montpellier"     } ] 

I want to include this output if type is equal to "big":

{     "greeting": "Hey man! How're you doing Aziz?",     "place": "Montpellier" } 
  1. In the first row, I combined two values into one with some additional words.
  2. I don’t want my whole output to be empty if there isn’t type value equal to "big". Instead, output values of type value equal to "small" as a fallback.
  3. If there is not a fallback with type equal to "small", then output an empty string or NULL value but without Nulling the whole query result. Or preferably, not include these keys if no value is present.

I know I can combine values into one by using CONCAT but don’t know how to build this query and integrate it. I can’t loop through the array and output the values of the object that matches my filter. Tried CASE but still wasn’t able to get the result I want.

I use PostgreSQL 13.

Missing Meta Keys on WooCommerce orders

I have a WPML problem with order language being ignored, and according to WPML it is because there is no meta keys in the metabox on orders. I only see attributes. Especially wpml_languages are missing.

I have a fresh install with the same theme, the same WP version and the same plugins – here I do not have this problem.

I have 3 sites from the same DB copy, one local, one for testing and one live. Everyone has this problem.

Have tried switching to a default theme, disabling all plugins, updating everything, etc. Nothing helps.

Any idea what could be causing this?

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id variant_id
123-123 ABC
123-123 NULL
456-456 ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.

CREATE TABLE IF NOT EXISTS inventory.price (   product_id             UUID NOT NULL, -- this has to be always to a valid product   variant_id             UUID,          -- this could be NULL   amount                 MONEY NOT NULL,   created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),   -- Constraints   CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),   CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)     REFERENCES inventory.product (id) MATCH FULL,   CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)     REFERENCES inventory.variant (id) MATCH SIMPLE,   CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money) ); 

And the inspection to information_schema confirms the non-nullable constraint.

column_name column_default is_nullable data_type
product_id NULL NO uuid
variant_id NULL NO uuid
amount NULL NO money
created_at now() NO timestamp with time zone

Are hardware security keys (e.g ones supporting Fido2) “able to protect authentication” even in case of compromised devices?

Correct me if I am wrong, please.

I understand that 2FA (MFA) increases account security in case an attacker obtains a password which might be possible via various ways, e.g. phishing, database breach, brute-force, etc..

However, if the 2FA device is compromised (full system control) which can also be the very same device then 2FA is broken. It’s not as likely as opposed to only using a password but conceptually this is true.

Do hardware security keys protect against compromised devices? I read that the private key cannot be extracted from those devices. I think about protecting my ssh logins with a FIDO2 key. Taking ssh as an example, I would imagine that on a compromised device the ssh handshake and key exchange can be intercepted and the Fido2 key can be used for malicious things.

Additionally: Fido2 protects against phishing by storing the website it is setup to authenticate with. Does FIDO2 and openssh also additionally implement host key verification or doesn’t it matter because FIDO2 with openssh is already asymmetric encryption and thus not vulnerable to MitM attacks?